If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
New Database - Primary Key
Bernard Peek wrote in
: There were once very good arguments for using surrogate keys even if a table had a real primary key, but I believe that they have mostly gone away. I will use a natural key if: 1. it's not functioning as foreign key in another table, AND/OR 2. it's a single-column key. I'll use a single-column natural key as a foreign key, but never a multi-column natural key. Also, natural keys make join tables extraordinarily ugly and difficult to work with. And the benefits are so very slight that it's not worth the complications. Perhaps it's a result of the particular apps I have been called upon to build, but I've almost never encountered a single table in any of the apps I've built (other then the tables that meet the criteria above) where a multi-column natural key was possible. My customer's data is never complete enough to allow it (i.e., they need Nulls). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#22
|
|||
|
|||
New Database - Primary Key
Bernard Peek wrote in
: There are lots of situations where there isn't any alternative to surrogate keys, in fact the majority of tables I have come across don't have a good natural key. But in any system that I manage the DBA will have to justify the decision to use surrogates on a case-by-case basis. Here you are being reasonable, and I was hoping for a catfight! I was actually expecting you to argue for a zero-length string as default value. I hate that (I despise the fact that A2003 changed the default text field to allow ZLS), because you can no longer test for Null, which in an indexed field is faster than testing for a ZLS (at least with Jet). I never do it, but some people allow ZLS and initialize to Null, and then use the ZLS to show that there is no value, not that it is unknown. That's valid, but it would drive me crazy to have both ZLS and Null in the same column. I'd always end up getting bad resultsets because I'd forget to test for both. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#23
|
|||
|
|||
New Database - Primary Key
In message , David W.
Fenton writes Bernard Peek wrote in : I've spent quite a lot of time in the comp.databases.theory newsgroup They are idiots in that group. There certainly are. But there's some gold amongst the dross. I've learned a lot over the years. I've just recommended this group to someone who foolishly asked for help there and just got abuse from certain parties.. None of them really work with actual client data, so far as I can tell. Celko is one of the worst blowhards -- a smart man, but doesn't really understand the real world. Theory is a starting place for designing a real-world app. It is not the end point. I used to run a management training course. Typically at least one person in each intake would declare that as management theories never work they weren't worth studying. That's why I had to start my courses with a session on the theory of theories, and how to use them. I had to point out that management theory is an aid to good management, not a substitute for it. Understanding the theoretical basis for something can help eliminate a lot of fruitless work and identify risks before they become bear-traps. -- Bernard Peek |
#24
|
|||
|
|||
New Database - Primary Key
Bernard Peek wrote in
: Understanding the theoretical basis for something can help eliminate a lot of fruitless work and identify risks before they become bear-traps. Absolutely. But theory shouldn't straitjacket your implementation. As I said, it's a starting point -- you try to normalize as much as you can until the point at which it causes problems that make the app unnecessarily complicated. Some people seem to believe that there's a single proper structure for representing any particular entity. I believe that there is not, as the proper structure will depend on the role that entity plays in the particular application. I could go on for pages elucidating that point, but I think you get it, so there's no need. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#25
|
|||
|
|||
New Database - Primary Key
"David W. Fenton" wrote:
I've spent quite a lot of time in the comp.databases.theory newsgroup They are idiots in that group. None of them really work with actual client data, so far as I can tell. Some folks in there are certainly idiots. Actually I do suspect many do work with real data. Trouble is I suspect a few/some/many don't work with the application development environment. So they don't have a clue what works and doesn't work in the real world. If so I pity the folks who have to work with them. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
Thread Tools | |
Display Modes | |
|
|