I would use a generated PK myself, just for the reasons you mentioned. Also, indexing and comparing by integer is faster than comparing by strings. You can put a unique index on the name field too without making it a primary key.
Turns out you can create a multi-column unique index on an MS access database, but it’s a little crazy if you want to do this via the GUI. There’s also a limitation; you can only use 10 columns per index. Anyway, here’s how you create a multi-column unique index on an MS access database. Open … Read more
You want to start designing a normalized database up to 3rd normal form. As you develop the business logic layer you may decide you have to denormalize a bit but never, never go below the 3rd form. Always, keep 1st and 2nd form compliant. You want to denormalize for simplicity of code, not for performance. … Read more
If you assume that SHA-1 does a good job, you can conclude that there’s a 1 in 2^160 chance that two given messages have the same hash (since SHA-1 produces a 160-bit hash). 2^160 is a ridiculously large number. It’s roughly 10^48. Even if you have a million entries in your database, that’s still a … Read more
I personally prefer your 2nd approach (and would use it almost 100% of the time) – introduce a surrogate ID field. Why? makes life a lot easier for any tables referencing your table – the JOIN conditions are much simpler with just a single ID column (rather than 2, 3, or even more columns that … Read more
I would take a different approach to your attribution scheme. Rather than treating different attributions as synonyms, I would treat them as overlapping, or more specifically, nested descriptions of a property. This would handle your business case while at the same time acknowledging the astute observation made by Mike Sherrill. Here is a quick ERD … Read more
No There is nothing wrong with Nullable FKs. This is common when the entity the FK points to is in a (zero or one) to (1 or many) relationship with the primary Key referenced table. An example might be if you had both a Physical address and a Mailing address attribute (column) in a table, … Read more
Take a look at Martin Fowler’s Patterns of Enterprise Application Architecture: Single Table Inheritance: When mapping to a relational database, we try to minimize the joins that can quickly mount up when processing an inheritance structure in multiple tables. Single Table Inheritance maps all fields of all classes of an inheritance structure into a single … Read more
Anders, do not give up any integrity or hardness, eg type safety. (Response coming). @Anders. No, not at all, subtyping is fine (the question is which form you use and what are the dis/advantages). Do not give up any strength or Integrity or type safety or checks or DRI. The form you choose will demand … Read more
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more