Reputation
8,010
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
1 13 27
Newest
 Good Answer
Impact
~234k people reached

Feb
8
comment In a one-to-one relationship, where should the foreign key be placed?
@MichaelGreen - It seems logical at first, but if you read OP's question you see this specifically: •But not all employees are required to be users of the system - So USER is not the supertype because some EMPLOYEES are not users.
Feb
7
comment In a one-to-one relationship, where should the foreign key be placed?
@Xegara - you would outer join Entity to both Employee and Customer and see which one is not Null (or which ones, if it is possible to be both). Keep in mind, I didn't say you have to or even should use the superset approach. There's nothing especially wrong with two mutually exclusive foreign keys, if it doesn't bother you and if it more or less stops there. The benefit of the superset approach is that it is more aesthetically appealing to some people and it is more appropriate if you have multiple additional attributes that are shared between the subtypes (customers and employees).
Feb
7
comment In a one-to-one relationship, where should the foreign key be placed?
@Xegara The keys are in the children (employee, user, customer) so there are no nullable foreign keys. There will still be cases where some of these relationships exist and sometimes they don't. That's the reality of your business rules. There's a difference, however between having two mutually exclusive foreign keys in one table and having multiple optional relationships to a single parent.
Jan
17
comment Animals hierarchy use-case: multiple farms, multiple species
@Meglio - Changing primary keys is a sure way to find yourself with orphaned child records. Since in the real world, it's possible that unique identifiers can change, many people like to create surrogate keys which never change. You can guarantee this because you control the surrogate key. The idea is that each new animal gets your private unique ID number (or GUID, whatever), which never changes. If the external IDs change that's OK, update that record. Build your parent/child relationships with the private keys that you control. Your users don't even have to see the private keys at all.
Jan
10
comment What is the db-structure behind sites with multiple similar subdomains, like Stack exchange?
Yes, that is the search term you should use.
Jan
10
comment What is the db-structure behind sites with multiple similar subdomains, like Stack exchange?
Welcome to DBA Stack Exchange. You should look around here and in StackOverflow. This question is already discussed in a few places. Here is a "classic" article on the subject: msdn.microsoft.com/en-us/library/aa479086.aspx
Dec
24
comment What is a basic model for making a database with users and groups?
@levi - Another possibility is that you really need to have a mixture of individual and role rights. In that case, your ROLE table can be subtyped into Individual and Group types, where the group type has zero to many members and the individual type has exactly one member. How you enforce these cardinality rules is up to you. It could be done declaratively in your database schema, in which case you need to change the pictured schema slightly. Or you might use application logic, in which case your schema still looks like what I've pictured above.
Dec
24
comment What is a basic model for making a database with users and groups?
@levi - If that happens then one way to handle it is to make a special role like "Bob's Special Permissions", which is a bit of a kludge, but it solves the problem as long as you don't have too many exceptions like this. It is possible that what you think is an individual exception may actually be a subtle new role that you haven't considered. What happens when Bob quits? Does Bob's replacement also need that exceptional permission? If so then you actually have a role with one member, not an exception.
Dec
20
comment library data modeling
@HyunSULee - You're welcome. Feel free to click the check mark to indicate that you accept my answer.
Dec
14
comment Relation between first name and last name
See my answer to this similar question: dba.stackexchange.com/questions/65808/…. There is a difference between coincidental occurrence and relationships.
Nov
20
comment Database design: Two 1 to many relationships to the same table
@AmadeoGallardo The answer is "it depends". Querying against a key is always pretty efficient, since you can generally count on an index scan at least, if not a seek, and these are quick operations. The issue becomes when you query across both keys in the two foreign keys solution. Here you're asking the query optimizer to do an either/or operation. At best this will double the cost of the query, usually a little worse, since you have to query against one key, then the other, then merge the results.
Nov
17
comment Should I create a child table without primary key?
It's hard to understand why you don't think you need a unique key. Please explain why your primary key isn't just the combination of receipt_id, prod_id and coupon_id. It looks like what you're getting at is this being a compound primary key. Are you asking whether you need to add a surrogate key?
Sep
30
comment Table naming and Database normalization
A contrary opinion to the answer you accepted: stackoverflow.com/questions/11756610/table-column-name-design/…
Sep
11
comment Should I split my tables into different tables when all of the data is dependant on the primary key?
Indirectly dependent is not dependent. Can different license codes with the same product IDs have different product names? If not then product name is not dependent on the license code. Go read en.wikipedia.org/wiki/Database_normalization and try to understand the basic normal forms, especially third normal form.
Aug
16
comment Storing resource information efficiently
Is this really a big problem? It seems to me the most complicated part is working out the display of items stored as date ranges, which you've obviously done already. For splitting/recombining existing records when changes are made, it seems to me you just have to find all of the overlapping records and either update them or delete them, depending on whether they overlap or are fully contained within the range being "updated" by the user. See stackoverflow.com/questions/20177304/… for how to find date range overlaps.
Aug
14
comment Normalization for postal addresses in US (address, county, city, state, zipcode)?
There is no point in normalizing ZIP codes. Normalization is for data integrity, not for lookup efficiency. There are no facts that are important to an address that are fully functionally dependent on a ZIP code, therefore there is no reason to create a table with ZIP code as its primary key. An index on ZIP is more than adequate for making retrieval by ZIP Code quick.
Aug
14
comment Normalization for postal addresses in US (address, county, city, state, zipcode)?
City and ZIP are many-to-many. Some ZIP codes cover huge areas and some cover a single door.
Jul
10
comment Product attribute schema design, mixing solutions
JSON array is just EAV stored in a really awkward format. Even relational EAV is better than a JSON array.
Jul
5
comment Snooping-safe Vote tables
Ranvijay, I agree with the direction you're suggesting. If you expand your answer to give more detail, perhaps an example and discuss how this gives the protection the original poster is asking and also what potential shortcomings your answer might have, then you will get more up-votes and soon you will build enough reputation to be able to participate in the site in more ways.
Jul
1
comment table structure for events
Google for intersection table and many-to-many relationship.