Database design 'Belongs to' db relationship best practices?

I'm working on a photography site. Photos on the site will always belong to exactly one event. My initial design was: Table: Events ID, Title, etc Table: Photos ID, ThumbnailURL, etc Table: EventPhotos EventID, PhotoID, SortOrder This seems natural to me, but I realize the relationship it describes actually allows a Photo to belong to many events. I've thought about refactoring it like this, which would only allow a photo to belong to one event: Table: Events ID, Title, etc Table: Photos

Database design MySQL idea for high-volume "rotation" of stats?

I have an online game where I record lots of gameplay stats. These stats tables get large very quickly, and I have to be careful because simply recording more stats can cause the game's performance to get pretty bad, once the table gets big enough. My strategy, which isn't a very good one, is to keep the stats tables small. I have an automatic process that creates a new table every 24 hours, preventing the performance from getting too out-of-hand. But my solution is ugly and is a sort of "ro

Database design Database design, multiple attributes in a single field

Is using one database column for each attribute in an ER-model considered best practice? I do maintenance on legacy code, and I see a lot of multiple attributes, as well as business logic squeezed into single database columns, and I wonder if there is ever any good reason for doing so. Example is using prefixes for integer values, say a customer ID where the first two digits represent the customer state, and the following digits, the actual id of the customer. Thus storing two attributes in on

Database design Need a simple, good (fairly priced) ER Tool

I've been using ERWin for about 10 years now. My version is ancient and I do not think it's worth paying the ridiculous upgrade price! Problem is it's starting to show its age - I've been using it primarliy for MS SQL. I wish I could justify Embarcadero's ER/Studio, but I just need something that will allow me to design a DB and sync it. I've checked out, but nothing fits except Schemabank. But a web based tool has too ma

Database design What is loop based programming?

This isn't a joke. ;) I was working with a DBA to define a data model and he was pushing back against some of my suggestions by saying something like "well before we had 'loop based' programming" or "loop based data models" My suggestions, because we are working with Entity Framework, a model == table schema based ORM, were to simplify some relationships. I want to know what he meant by that? Thanks for the answers so far but its definitely not a sql "set-based" vs cursor,while loop thing

Database design Email Notification Preferences

I want to allow my users to opt-out of certain email notifications. I'm trying to decide how to implement this in the database. I've already got a set of Users and all the emails are stored as EmailTemplates which have a unique key. So I figure a simple m2m table with users/emails would work well. However, should I (1) populate this table with all user/email combinations every time a user is created (may complicate things when a new email is added?) so that by default they're opted into everyt

Database design Datawarehouse Fact table question

I have a fact table called Loans. In this table in a loan identifier, the date the loan was made, and the amount of the loan. The business requirement I don't quite know how to do in the datawarehouse is the following. The loan amount can be adjusted in the future. So lets say August 1st we make a loan with an id of 1 and a amount of 20,000. October 1st this loan is adjusted to 19,000. Do I put two entries in the fact table with the same identifier, and different dates and amounts? Do I

Database design Optimal Database Structure for MicroStrategy

I have spent most of my career developing data warehouses\marts as Star Schemas because they were typically used in conjunction with Microsoft's Analysis Services. However, we are starting to leverage MicroStrategy 9.0.1, and I have been told that Star Schemas are less than optimal for this platform. MicroStrategy does not have an official stance on this topic so I thought I would ask this community. Should I still continue to use the denormalized structures, or should I consider a more norma

Database design Two-key table in database?

I'm trying to design a database table that lists "prices" for distances, "City A" to "City B". City B to City A should be the same price, so it would be redundant to store this information twice. How should I design the table such that given 2 cities, I can look up the price without having to store it twice (as A,B,price and B,A,price)? My idea is that I can store it "alphabetically" such that the "earlier" city would always be in the left column, and the later city would appear in the right

Database design Organizing database for multiple-source data entry

I have a web application that contains data structured like this: Review (has properties such as title, type, text, year, etc.) Software (in many-to-many relationship with reviews) Language (in many-to-many relationship with reviews) Owner (in one-to-many relationship with reviews) There is a web form that is used to manage all reviews, software, languages, owners, and so on, so that data is correctly displayed on the site. Now, there's a need to allow third parties to modify data in those

Database design Email belongs to User - Using Email as username Cakephp 2.0.5

I have a table email_addresses and a table users. I am using the users email address as their username. So the users table would be something like: email_address_id password The email_address_id field would just find the email in the email_addresses table for the username as login information. I understand I can change the username field to another name like email_address with the auth component, but how would I use a field from a different table as a username? Right now I have to insert

Database design Database design for same site for different clients

What if I'm going to build a site which is going to be used and maintained by multiple clients. The clients only have access to their own data. And I almost never have to access the data myself. It's a totally different website, but it's going to be something like GetSatisfaction the way it is going to be setup. E.g. the clients will have the control over their own 'community'. Will I setup 1 database with all the communities in it with access control or should every community have their own da

Database design Should I normalise a date field that contains many duplicates?

In my scheduling database, I have an "assignments" table with the following fields: | assignment_date | start_time | end_time | task_ID | staff_ID | Each staff member will have six or seven separate assignments per day, meaning that with the current staff numbers there will be approxmiately 250 individual entries for any individual date. I was wondering if there are any situations where it would be worth extracting the date field into a separate table ( i.e. changing assignment_date to ass

Database design How do I model a join table with parent-child tables?

I have a table, VisitTest, that joins patient visits (Visit table) to diagnostic tests (Test table) given during that appointment. The Test table is a superclass (parent table) of tables containing actual tests. Each child table has the test parameters and results (1). The Test table contains metadata. Columns like id, name, description, status, etc are stored here. The child tables (inherit, in OOP terms, from the Test table) and have their own test-specific fields. Each test parameter is its

Database design Database design for selecting a main element from a list

A user has a list of books, but only 1 book can be displayed on his profile as he should select it as his favourite book. How do I design my schema to set a book as being 'the' book? If I have a field IsFavourite in the book entity, then all books can be marked as favourite. If I'm setting a favourite on 1, I have to retrieve his favourite book and unmark it as favourite (3 database calls). If I have a preference entity that has a reference to a book, I can just set that book to be the favouri

Database design How to design schema relation in SQL to mongoDB?

I still confuse after read mongoDB The Definitive Guide and this link is there standard for mongodb to design the schema database. I have the schema in MySQL database like this below my database above is fixed & just for DDL. Please help me show the step to design schema relation above to mongoDB. thanks in advance

Database design Database structure for account transactions

i have created a database to store customer banking transactions. i am currecntly storing all th trasactions in one table with columns for the cust_id, tran_no, tran_date and so on. Currently my database is small since i am only testing it and it works properly. I perform queries to get transactions by date and also sum of transactions by customer. My question is, is it ok to maintain all the transactions in one table. Do i need to split the transactions for each customer in seperate tables.

Database design Design a database for a "form engine"

I'm trying to create a basic form generator. So a user can click together an HTML form in some sort of CMS system. I'm still going over the database design for this, and i have a question when it comes to checkboxes. My database design looks like this: fields pk_field_id | field_type | field_name | default_value 1 | textfield | text1 | Some default value 2 | textfield | text2 | NULL 3 | radio | radio1 | NULL 4 | checkbox |

Database design Modelling data common to many tables

I have a dynamic data model with potentially thousands of entity tables, let's call them E1, E2, ..., or generally EX. There is also a small number of utility tables, that contain data that may be attached to these entities, for example an audit table or an 'attached documents' table, let's call them U1,U2, ..., or generally UX. The relation for each pair EX-UX is 1 to many (for example each instance in E1 may have several attached documents, but each document is attached to just one instance

Database design Tool for EER Modeling

Is there a good free conceptual modeling tool for EER modeling? It should support specialization, generalizaton, union, etc. I came across MySQL Workbench but it seemed to belong to the physical modeling stage(examples that I saw included 'VARCHAR' or 'INTEGER' specifications for attributes and it deals with tables rather than entities).

Database design Database design User Group Orders

I am making an application where an superuser can take orders from members and can create groups of multiple members which also can take orders. Right now i have the following design: Members id(pk) name isGroup GroupMembers id(pk) GroupId = also the id of a member in members table memberId(Fk -> Oders id memberid (Fk -> product id (Fk -> Also thought about: Members id(pk) name Groups id(pk) name GroupMembers id(pk) GroupId =

Database design Use as database schema a rational approuch?

In a project for a news/content platform, our team is debating around using vocabulary as a direct source to model our database and our domain logic. Example: The NewsArticle entity has the following hierarchy on Thing > CreativeWork > Article > NewsArticle Our domain would have one class for each of them and use extension to make the hierarchy. The same pattern would be put on the database, meaning we would create four tables(or maybe use document dbs). Fie

Database design Relational Database Design - To add a circular reference or not to add a circular reference

Suppose you have a table of insurance policies like Policies PolicyID | CustomerID 1 | 1 2 | 1 3 | 2 You want to track the revisions made to each policy, so you create a table like PolicyRevisions PolicyRevisionID | PolicyID | EFDT | EXDT | RevisionDate 1 | 1 | 1/1/2014 | 1/1/2015 | 1/1/2014 2 | 2 | 1/1/2014 | 1/1/2015 | 1/1/2014 3 | 3 | 1/1/2014 | 1/1/2015 |

Database design using Natural key as the ID of DomainObject or GUID + auto-increment Domain Driven Design

I've been reading a lot of articles about DDD and noticed that most are using GUID as their ID when persisting to a database. They say that GUID scales well and auto incrementing ID's are a big no-no when it comes to scalability. Im confused now whether to use GUID or auto-increment. Basically the Domain is about membership system (binary tree). (tracking of register members) The first requirement is that we should have something that uniquely identifies them in the system (we call it Account

Database design E-R modelling and business rules

How can I insert in an E-R model a concept like this: "an user can insert an evalutation of another user who participates to the same event" where the participation must be deducted from an approved subscription to an event. I made a recursive relation "Evaluation" from Subscription (which is related to User and to Event) to itself, but I'm not sure it is correct. Maybe for concepts like this I need to use a business rule? Thanks.

Database design how to design entities in typeorm if there is a center user table and different role tables

suppose I am designing a school website, and there are many roles,like students , teachers, admins... my DB design looks like this then, consider easy to read and easy to save how to design entities in typeorm is better? [UPDATED] finally, i design like this,i just merge all role into a ROLE table,and role_type means different role, eg. 5 means admin, 3 means teacher, 2 means student. however, there are 2 columns redundancy, the school_id and the class_id. these 2 columns are not necessary

Database design How make the relationship splitting the score of soccer in cypher

In EPL match, the results between two teams is 2-6, so how to make the relationship and show that the high goals scored team won the match. Here is an attempt to load the CSV data. LOAD CSV WITH HEADERS FROM "file:///EPL_dataset_for_2018_19_assignment.csv" as row MERGE (team1:EPL_Teams{name:row.Team1}) MERGE (team2:EPL_Teams{name:row.Team2}) MERGE (round:Round{name:row.Round}) MERGE (date:Date{name:row.Date}) MERGE (score1:Scores{name:row.HT}) MERGE (score2:Scores{name:row.FT})

Database design Presto vs Impala: architecture, performance, functionality

Could you highligh major differences between the two in architecture & functionality in 2019? And how that differences affect performance? For some reason this excellent question was tagged as opinion-based. Extra-question: why Amazon decide to go with Presto as engine for Athena? Is it anyway better than Impala? UPD f PrestoDB and Impala are same why they so differ in hardware requirements? Presto asks 16 GB+ of RAM while Impala asks for 128 GB+ of RAM.

Database design storing technique in sql

I have a Groups and in them I have users, how should I store them in Database? one way I thought of is store all the groups in one main table and the users in the secondary table that holds all the group's users, but the downside that I will need to open a table for each group. another way is using a json like format to store the users in column users in the main Group table. another way is using 2 tables one for the group and another one will hold the columns: group, user. but in this form

Database design Designing login system with different roles

I am designing a database that is for a clinic management system using mysql DBMS, Clinic Owner can have an account through which he can give access to all staff or a specific persons to access clinic account depending on the permissions that granted to that user I have the following tables: Users Table user_id email username Managers Table. user_id_fk Doctors Table. user_id_fk Nurses Table. user_id_fk Employee Table. user_id_fk Permissions Table. user_id_fk The user which ca

Database design Something like inheritance in database design

Suppose you were setting up a database to store crash test data of various vehicles. You want to store data of crash tests for speedboats, cars, and go-karts. You could create three separate tables: SpeedboatTests, CarTests, and GokartTests. But a lot of your columns are going to be the same in each table (for example, the employee id of the person who performed the test, the direction of the collision (front, side, rear), etc.). However, plenty of columns will be different, so you don't want t

Database design Object-oriented Doctrine schema

Hey all, I'm learning Doctrine + Symfony and I may have chosen too complex a data model for my own good. Here's an overview: Users create Gizmos. There are 5 Modules to choose from. Users cannot define new ones, only instantiate them. A Gizmo has any number of Instances in any order. An Instance has one Module ID. Instances are configurable, but the available settings depend on the Module ID. A Foo Module may require an integer and a string, while a Bar Module may require a arbitrary-length l

Database design table design for storing large number of rows

I am trying to store in a postgresql database some unique identifiers along with the site they have been seen on. I can't really decide which of the following 3 option to choose in order to be faster and easy maintainable. The table would have to provide the following information: the unique identifier which unfortunately it's text the sites on which that unique identifier has been seen The amount of data that would have to hold is rather large: there are around 22 millions unique identifier

Database design Forum design suggestions

I'm doing a forum script in php, i'm trying to think the best way to flag topics each user has read or not. Once the user logins into forum i can mark what's been read or not depending on his last login time, that's fine, now what's the best approach to mark each topic each user clicks? I was thinking in adding cookies but that would lead to a security issue and might not be accurate, specially if the user logins from another machine/browser. I also thought of adding a special database table

Database design Many-to-Many relationships needing an associate table

In many database design tutorials/articles, they always bring up the fact that if two tables share a many-to-many relationship, then a third table should be created to act as an associate table to link the two. However, they never provide the reason WHY we should do it that way. I'm curious to know why and examples of problems that could occur if you just kept the two tables as it is without the associate table.

Database design Help with (CakePHP) database design

I am confused about how to set this up using Cake's conventions. I have a table of users (pretty standard: id (pk, auto-increment), username, password), a table of groups (also pretty standard: id, name, user_id) and a practice table (like a doctor's practice, not like "practice makes perfect"). The practice table will be owned by one user (that belongs to a dr group) and will zero or more other users that belong to the practice (and are standard members). here's a very rough drawing of what

Database design Database design - question about efficiency (and general design quality)

I fear I don't know what I'm doing. 1: I have a table called ticket which has a column called total. When the total is updated I want to keep a record of it (old total, etc), so I decided to remove the total column and create a table called ticket_total with columns ticket_id, total, and datetime (the most recent of course is the "current" total). or 2: Then I realized that I will later want to give my clients the ability to sort tickets by total, or pull reports that aggregate the tot

Database design Which DB Junction approach is more efficient in this scenario?

In order to avoid soft delete I am creating a recycle bin database. The main database will junction to it. Here is an example of two possible junction approaches, and I was hoping for some input on which would be more efficient? For simplicity, lets say there are two tables, Order and Invoice (and each invoice only has 1 order). Order ----- OrderId InvoiceId Description Date NumberOfStuffOrdered Invoice ------- InvoiceId Description Price Tax Shipping For a junction of these tables to the r

Database design Struggling with database design/relations

I'm trying to make a database for a construction depot (there where are stored materials for construction). I need to know all the materials stored (products in), all the products sold and all the products that had left. I'm thinking to do like this: Products--> Deliver <--Depot (many to many) - here I see all the products in. Depot--> Sell <--Products (many to many) - here I see what I have sold. And what is left: To make a difference between "Deliver" and "Sell". Is there something

Database design How to design a database to create associations between multiple modules?

Lets say our web application consist of many modules. Each module has its own table. Now if i need to keep relationship between my module and another module, ideally i will create another table like module1_module2_relationship which will have module 1 pk and module 2 pk. Assuming I also need to store some more columns regarding this relationship I will add some more columns to this relationship table. But my module can have relationship to many modules and I dont want to create new table for

Database design Software design: DRY, Single source of truth, and data validation

From Wikipedia's article on DRY Software: The DRY principle is stated as "Every piece of knowledge must have a single, unambiguous, authoritative representation within a system." The principle has been formulated by Andy Hunt and Dave Thomas in their book The Pragmatic Programmer. They apply it quite broadly to include "database schemas, test plans, the build system, even documentation". From Wikipedia's article on Single Source of Truth: In Information Systems design and theory, as in

Database design seed composite keys in pivot table laravel 4

I used JeffreyWay Laravel-4-Generators in my laravel 4 project. I have a users and a privileges table and a pivot table user_privilage. I used a generate:seed to seed a user and privileges table, but I don't know how to seed the user_privilage table. I try to make a site and I want to seed basic items to the tables to view how the website works. How to do that?

Database design Database Structure - Items from multiple tables

I am creating a database for storing wrestling events and have hit a wall with how to structure my database. Appreciate anyone's assistance. I have the following tables: tblPeople that will contain all of my wrestlers. tblPeople - personId - personName #eg: "211", "Dean Ambrose" tblTeams that will contain a specific tag team tblTeams - teamId - teamName #eg: "13", "Shield" tblPeopleInTeams will relate people to teams tblPeopleInTeams - peopleInTeamsId - teamId - peopleId

Database design Database structure for a complex report form

I am trying to design a database for a report form. This form has 10 Sections. each section is related to a Department. As we receive this report each section or report will be sent to corresponding Department. form fields may contain numeric, date, string or texts. and some sections also define one-to-many relation. I would like some suggestion should i define one table for the complete Report or a table for each Section? What can be the pros and cons of each technique?

Database design Using Parse as BaaS to store multiple images in an efficient manner

I am using parse to make an ecommerce application for IOS and Android. I have a Product Table (or class) that has all the product related information like Price, Description etc. I am struggling to find the most efficient way of storing images Every product has about 10-15 images and I want a good UI at client end in terms of image download. Following are the methods I can think Create additional 15 columns in Product table with Type to store images. Create an Array in Product table and stor

Database design Database design 3rd Normal Form

I have a database with many tables, 4 of which are these Payment Credit Card Paypal Bitcoin Credit Card attributes: cardID (PK) type number expireDate ... PayPal attributes: paypalID (PK) account ... Bitcoin attributes: bitcoinID (PK) ... Payment Table attributes: amount ... ... cardID (FK) paypalID (FK) bitcoinID (FK) A payment can only be paid by either a card/paypal/bitcoin so I am breaking 3rd normal form because if the client uses a card then I know that he didnt use

Database design Using Firebase User UIDs for keys of related DB entries - Good or Bad Practice?

Are there any problems with using a user's UID (which is generated by Firebase upon user creation via Firebase Auth) as the key for associated database entries? For example, here: /config/user.uid/configObject ...each user has one config entry. Due to Firebase's database rules (where settings on a parent node apply to all child nodes) it's often necessary to create a separate config tree (following the example, where a /users tree needs to be read and write protected). The only reason I ca

  1    2   3   4   5   6  ... 下一页 最后一页 共 15 页