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

Apr
4
comment library data modeling
@TomAu I think I would number them with a meaningless unique number encoded in a barcode sticker glued inside the cover of the book. I don't think each copy needs to have an "external" (i.e. human-friendly) identifier. This isn't necessary since people generally don't care about which copy of a book they have. The copy ID is relevant to the system so it should be assigned in a way that is convenient to manage. Pre-printed unique ID barcode stickers are convenient for the librarian who is cataloging the book. You can glue in the sticker, scan it, and then associate it with the title.
Apr
4
comment library data modeling
@TomAu Naming is one of the hardest parts of data modelling. I would be inclined to use the term Book for the physical volumes. As I noted in my answer I would call the abstract collection of all printed copies containing the same content Title. If someone thought Book was too ambiguous, I think libraries often use the term Copy.
Mar
29
comment What problems are solved by splitting street addresses into individual columns?
Using pre-parsed fields for addresses in no way ensures that you can avoid duplication of an address. In Canada and in the U.S. (perhaps elsewhere too) there can be multiple, valid municipality and street names. I'm sitting in my office on Hurontario St which is also called Hwy 10. I grew up in Willowdale (or North York or Toronto) all of which are valid as per Canada Post. Address parsing is an insanely complex problem domain and there are many packages that specialize in solving this problem. Buy one (or many) of those and forget trying to DIY.
Mar
14
comment Find all users who rated all the movies rated by a specific user
Are you looking for other users who rated all of those movies, at least one or those movies, or, as Lennart asked, exactly the same (and only those) same movies?
Mar
11
comment How to store historical records?
@DatabaseNewbie - This is a trade-off. UNION is more expensive (in CPU cycles and code complexity) than a select from a single table. However, repeating current data in both the current and historical tables is more expensive (in hard disk) than keeping them separate. Where is your sensitivity and how would you strike this balance? I prefer simpler code which is more responsive. That's my bias. What is yours? (i.e. you have to decide based on your circumstances)
Feb
21
comment Database design for flights, airlines and aviators
As per Verace's comment, I think your model may not be complex enough to model real world commercial air operations. In any case, there really isn't an effective way to use declarative referential constraints to solve this kind of problem. You can cheat it with table structures, but this ends up being more trouble than it's ultimately worth. You need to enforce the "pilots only fly their own airline's flights" business rule in your application logic.
Feb
16
comment How to model A Shop Database
+1 for double entry bookkeeping. This is the only proper way to track financial transactions in a professional system.
Feb
15
comment ERD for a web development company
I think you need to move from entities and relationships at the conceptual level down one level to logical, where you start to concern yourself with cardinalities. At the conceptual level almost anything can be seen as being related to almost anything else, because they are. However, sooner or later you're going to need to boil this down to the essential relationships. This will happen as part of an initial schema normalization process. At this lower level things will be clearer and you'll understand why I ask, "Are orders split between customers". This is a cardinality question.
Feb
15
comment How to model entities in an entity relationship diagram?
@Trix because they aren't related in any way to SERVICE_LINE_ITEM. They are subtypes of SKU. I didn't think it made any sense to make any visual linkage.
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.