October 30, 2015 at 5:22 am
Eric M Russell (10/29/2015)
Maybe I'm missing something, but the table appears to lack a unique index on the natural key, which would allow duplicates. This would normally be a requirement for any table, but since this table contains a collection of things (comics), I guess it's possible to have duplicates in reality.
Eric, I have to disagree with the notion that a natural key is a requirement for any table. There are plenty of things in the real world (comics among them) where there isn't a possibility of having a natural key. Comics don't have individual serial numbers, so one can buy lots of duplicates of them on purpose or by accident. Hence the reason I used an surrogate key for the article.
Since this is a Basics article, I didn't want to throw in a lot of index discussion either. So I'm tabling the majority of that for a later article.
October 30, 2015 at 7:46 am
Brandie Tarvin (10/30/2015)
Eric M Russell (10/29/2015)
Maybe I'm missing something, but the table appears to lack a unique index on the natural key, which would allow duplicates. This would normally be a requirement for any table, but since this table contains a collection of things (comics), I guess it's possible to have duplicates in reality.Eric, I have to disagree with the notion that a natural key is a requirement for any table. There are plenty of things in the real world (comics among them) where there isn't a possibility of having a natural key. Comics don't have individual serial numbers, so one can buy lots of duplicates of them on purpose or by accident. Hence the reason I used an surrogate key for the article.
Since this is a Basics article, I didn't want to throw in a lot of index discussion either. So I'm tabling the majority of that for a later article.
I didn't know this article was going to be the first in a database modeling series, so I apologize if I'm jumping too far ahead. However, off the top of my head, I can't recall ever seeing a properly designed table that couldn't have a natural unique key, even if a surrogate key is added as primary. I'm sticking to that assertion, and I think that's an important point to make in an introduction to relational table design. A surrogate key shouldn't be the ONLY key.
For example, in the case of a table that contains an inventory of commodity items absent unique identifiers from the manufacturer, there will typically be one row for each SKU (or in this case publisher, title, volume) and then a column for quantity.
Another example would be a POS system, where the natural key for each transaction header would be TerminalID (or EmployeeID) + TransactionDateTime, not simply a sequentially assigned OrderID (which has it use but shouldn't be the only identifier). A transaction is an event that occurs distinctly in space and time.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 30, 2015 at 8:37 am
Brandie Tarvin (10/30/2015)
Eric M Russell (10/29/2015)
Maybe I'm missing something, but the table appears to lack a unique index on the natural key, which would allow duplicates. This would normally be a requirement for any table, but since this table contains a collection of things (comics), I guess it's possible to have duplicates in reality.Eric, I have to disagree with the notion that a natural key is a requirement for any table. There are plenty of things in the real world (comics among them) where there isn't a possibility of having a natural key. Comics don't have individual serial numbers, so one can buy lots of duplicates of them on purpose or by accident. Hence the reason I used an surrogate key for the article.
Since this is a Basics article, I didn't want to throw in a lot of index discussion either. So I'm tabling the majority of that for a later article.
Used to be a collector myself once upon a time. It's quite possible, in fact common among serious collectors, to have multiples of a single issue. Depending upon how detailed you want to be, the grade of each copy could be different. Not counting special runs, novelty covers, etc, so on and so forth.
October 30, 2015 at 9:45 am
mwpowellhtx (10/30/2015)
Brandie Tarvin (10/30/2015)
Eric M Russell (10/29/2015)
Maybe I'm missing something, but the table appears to lack a unique index on the natural key, which would allow duplicates. This would normally be a requirement for any table, but since this table contains a collection of things (comics), I guess it's possible to have duplicates in reality.Eric, I have to disagree with the notion that a natural key is a requirement for any table. There are plenty of things in the real world (comics among them) where there isn't a possibility of having a natural key. Comics don't have individual serial numbers, so one can buy lots of duplicates of them on purpose or by accident. Hence the reason I used an surrogate key for the article.
Since this is a Basics article, I didn't want to throw in a lot of index discussion either. So I'm tabling the majority of that for a later article.
Used to be a collector myself once upon a time. It's quite possible, in fact common among serious collectors, to have multiples of a single issue. Depending upon how detailed you want to be, the grade of each copy could be different. Not counting special runs, novelty covers, etc, so on and so forth.
I can see how it could be important to have a separate row for each distinct comic; perhaps there are other non-key attributes like condition, price paid, or retailer that differ from one item to another. Perhaps the missing key column here is something like AcquisitionDate, which is something useful to have anyhow. It seems to me that you want to know Sandman Volume #1 purchased back on May 10, 2011 versus the same issue purchased on September 12, 2015.
So the unique key would be (PublisherID, TitleID, IssueID, AcquisitionDate), or perhaps (SeriesID, IssueID, AcquisitionDate).
Also, finding that unique natural key makes more sense when the table has normalized key columns rather than de-normalized text (ie: PublisherID and TitleID versus PublisherName and Title, which is yet another database modeling topic.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 30, 2015 at 11:39 am
I'd just like to thank those replies from people who have collected before. You're giving me all sorts of ideas for the later articles.
Keep 'em coming.
October 30, 2015 at 2:24 pm
Brandie Tarvin (10/30/2015)I have to disagree with the notion that a natural key is a requirement for any table. There are plenty of things in the real world (comics among them) where there isn't a possibility of having a natural key. Comics don't have individual serial numbers, so one can buy lots of duplicates of them on purpose or by accident. Hence the reason I used an surrogate key for the article..
I hope you give this some more thought before you write your next article. A database table that accurately models the real world by definition will always have identifying attributes in it (AKA "natural keys"). You really think that possessing multiple copies of a comic justifies ignoring database normalization and other design principles? Do you imagine that the publishers, distributors and retailers of those comics all think like that? Most of all, is that actually the kind of lesson you want an inexperienced reader to take away from an article about database design?
November 11, 2015 at 2:01 pm
I enjoyed this article a lot. There's a lot to be gained from learning by making mistakes, so your intro to your series is good.
I'm looking forward to reading the rest.
December 4, 2015 at 4:13 pm
Nice article, I like the approach you used to explain concepts. Looking forward to the next one!
March 29, 2016 at 12:05 pm
Brandie Tarvin (10/30/2015)
Eric M Russell (10/29/2015)
Maybe I'm missing something, but the table appears to lack a unique index on the natural key, which would allow duplicates. This would normally be a requirement for any table, but since this table contains a collection of things (comics), I guess it's possible to have duplicates in reality.Eric, I have to disagree with the notion that a natural key is a requirement for any table. There are plenty of things in the real world (comics among them) where there isn't a possibility of having a natural key. Comics don't have individual serial numbers, so one can buy lots of duplicates of them on purpose or by accident. Hence the reason I used an surrogate key for the article.
Since this is a Basics article, I didn't want to throw in a lot of index discussion either. So I'm tabling the majority of that for a later article.
I know this is an older thread but I wanted to add my voice to the discussion of a natural key -- I'm pretty much in favor of the idea of having a natural key, myself. It seems hard to believe that, if you are going to individually identify items in the database, you wouldn't have some individuality in the data model itself. For a comic book, it would seem that you would have some distinguishing feature even if it is not something created by the publisher. For instance, your natural key might include a notes field where you indicated how you distinguish between the different copies (in the degenerate case you could use "1, 2, 3 . . .", but then you would need some way of correlating actual copies to the numbers -- of course you have the same issue with the surrogate key). If you really don't distinguish between different copies then why use more than one row in the database? Why not just put in a quantity field, in which case your natural key becomes "publisher/title/whatever..."
Despite that, I enjoyed the article. Thanks for sharing.
- Les
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply