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

2d
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.
Feb
7
answered In a one-to-one relationship, where should the foreign key be placed?
Feb
3
answered Is it possible to display this restriction in an ER diagram?
Jan
26
awarded  Good Answer
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
17
answered Animals hierarchy use-case: multiple farms, multiple species
Jan
17
answered should I denormalize for user profiles?
Jan
13
answered Can different tables have columns with the same name if those columns are not primary keys?
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
Jan
1
awarded  mysql
Dec
31
revised What's the best way to diagram this “relation” looking for performance and|or disk space savings
Added a clarification
Dec
31
answered What's the best way to diagram this “relation” looking for performance and|or disk space savings
Dec
28
answered Database normalization: Which is the best fit?
Dec
28
revised Database normalization: Which is the best fit?
Corrected grammar and spelling and clarified language and formatting
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
24
awarded  Enlightened