The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Access question - Using a calculated field as a primary key?
ElJeffe
Registered User, ClubPA regular
Registered User, ClubPA regular
I guess I want to know both if this is possible (it seems the answer is no, since it's not letting me) and if it's wise (probably also no).
I'm building a table that must include the following fields:
- FamilyID, which is an ID number that is unique to a given family
- ReviewDate, which is the date a review of the family was performed
- <bunch of other stuff which is not important>
Originally, I was informed that each family would only get one record, so I used FamilyID as my primary key. Whoops, that changed! Now there can be multiple records with the same FamilyID. However, multiple records for a given family would necessarily occur on different dates. So I had the idea of creating a unique number for each record based on concatenating the FamilyID and ReviewDate into a single 12 digit number, and using that as the primary key. In hindsight, this was probably dumb. Still, each record needs to have a unique field that is searchable, and preferably something that is meaningful.
If I used a simple autogenerated number for the primary key, and then also defined a field based on combining the FamilyID and ReviewDate fields, I'd still have a unique number for each record that was searchable and meaningful, and without doing wonky things with the primary key. Would that be a viable solution? Is there a better way? I want to make sure I don't do something which seems reasonable at the time and then fucks me down the road once we have loads of data already inputted.
I'm building a table that must include the following fields:
- FamilyID, which is an ID number that is unique to a given family
- ReviewDate, which is the date a review of the family was performed
- <bunch of other stuff which is not important>
Originally, I was informed that each family would only get one record, so I used FamilyID as my primary key. Whoops, that changed! Now there can be multiple records with the same FamilyID. However, multiple records for a given family would necessarily occur on different dates. So I had the idea of creating a unique number for each record based on concatenating the FamilyID and ReviewDate into a single 12 digit number, and using that as the primary key. In hindsight, this was probably dumb. Still, each record needs to have a unique field that is searchable, and preferably something that is meaningful.
If I used a simple autogenerated number for the primary key, and then also defined a field based on combining the FamilyID and ReviewDate fields, I'd still have a unique number for each record that was searchable and meaningful, and without doing wonky things with the primary key. Would that be a viable solution? Is there a better way? I want to make sure I don't do something which seems reasonable at the time and then fucks me down the road once we have loads of data already inputted.
I submitted an entry to Lego Ideas, and if 10,000 people support me, it'll be turned into an actual Lego set!If you'd like to see and support my submission, follow this link.
0
Posts
http://stackoverflow.com/questions/6335094/how-to-define-composite-keys-in-ms-access
However, when looking for details of how it works and what it means to have a composite primary key, lots of people seemed to be saying it was a bad idea, for reasons I didn't quite follow. Is there some unintended consequence of doing this? If it's just a size or performance issue, I don't much care, because the database isn't going to be large enough that inefficiency would be noticeable. If it seems fine so far, and survived some testing to make sure it functioned as-intended for various combinations of data, am I cool?
Otherwise the solution is to add yet another auto incrementing index to keep track of, and filling up your tables with useless junk. I almost exclusively use composite keys now-a-days because managing enormous tables with IDs is asinine.
Just saying, if you're getting flak for it.
Basically you want to avoid adding an arbitrarily unique key (like an autoincrement number) when you already have a perfectly valid candidate key (family id + date).
It is "what does having a single integer primary key get me?" About the only answer that is valid is "somewhere in our system we can't handle composite keys. :rotate:" It is something that is fortunately not frequent anymore, because it is bad stuff in bad components.
Also important is the userbase, as if you don't have well-versed users or good documentation, adding an non-intelligent id is often worth it to prevent reporting impacts as it screams "HEY, THESE AREN'T UNIQUE"
Which is not a very compelling argument against it or garnering much sympathy from me.
I tend to use arbitrary "auto incrementing" numbers as my primary keys, unless I am using a unique ID that our lab generates, but I tend to be working with sub 50k records. I am guessing adding a primary key only really becomes an issue with huge data sets?
GIS is evil
This is a schema issue, not a scale issue (until you get so large that you have parallel/sync issues, and then you have more yes).
Sometimes you need to make an ID because the data itself is not a candidate. That is when you see autonumbers, although typically it is better to have some kind of GUID / hybrid.