Lookup Tables and Composite Keys

Thales750

Formerly Jsanders
Local time
Today, 06:05
Joined
Dec 20, 2007
Messages
3,610
In a system where it is necessary to use composite keys; what do ya’ll think is the best way to manage lookup fields?
The biggest problem I see is the “Not in List” event.
One solution would be to concatenate the keys and use them as text instead of a number.
Does anyone know a better way?
 
Can you provide a specific example to illustrate your concern?
 
tblBouncingBall

BouncingBall ID AutoNumber
BouncingBallLocID Number
BallColor Number
BallColorLocation Number
BallDiameter ETC
BouncingBall ID and BouncingBallLocID create Composite Key for Main Table



tblLookupBallColor
BallColorID AutoNumber
BallColorLocID Number

The tblLookupBallColor is used to store possible colors for bouncing balls records.
So far so good. But sometimes, Users may add a color when they are offline, in fact, every time they add a new color the system will treat it as offline and use local table to determine the Autonumber and the locationID.
So the only way to distinguish between say a red (BallColorID = 12, and BallColorLocID = 1) from the location 1 and a green one ((BallColorID = 12, and BallColorLocID = 2) added at location 2 is by having a composite key.
When I look at a view of this dataset from tblBouncingBall where BallColor = 12, which one will be displayed, Red or Green?
 
BouncingBall ID and BouncingBallLocID create Composite Key for Main Table
The autonumber is unique by itself. Adding a second column won't make it more unique.

Having to add data offline is problematic. I would use a compound field. Part 1 would be the "location" and part 2 would be a generated sequence number.

Much as I dislike "mushing" data into one column, you can't use combos unless you do that.
 
Then the question becomes, should you also save the components keys in non indexed columns (fields)?

That way if corruption ever did occur the keys could be reconstructed. And not indexing them aids in performance.

I realize this would be keeping reduntant data, and adding a few more fields to the system but modern computers have a lot of power and even more hard storage.
 
Last edited:
Not true, if you are offline, you cannot use autonumber to create unique fields. I'm a little past needing to learn how to create composite keys.
 
We are going to save the composite key as a concatenated text field with the location ID fixed at 4 digits and a a text version of a locally generated autonumber.

This will become the primary key.

The system will then verify that it is online with the main databses and append all of the local records to the SQL Server, it will then delete the local records.

Local system can then run even when the internet is down without the possibility of duplicate primary keys and they can be used in every situation that you would normally use an autonumber primary key.
 
Not true, if you are offline, you cannot use autonumber to create unique fields. I'm a little past needing to learn how to create composite keys.

You are incorrect. You most certainly can and it is when the Autonumber is set to be ReplicationID. There is a one in 2^128 chance of a duplcate. You could have one billion people input one billion records a year for 10 to 20 years and still not likely get a duplicate.
 
Last edited:
You are incorrect. You most certainly can and it is when the Autonumber is set to be ReplicationID. There is a one in 2^128 chance of a duplcate. You could have one billion people input one billion records a year for 10 to 20 years and still not likely get a duplicate.

Or you could get 500 duplicates, chance is a funny thing. You have to take into account cluster theory and other anomalies.

Why do gamblers, sometimes get rich?


Adding a location key to a record completely eliminates any possibility of duplication.
 
Or you could get 500 duplicates, chance is a funny thing. You have to take into account cluster theory and other anomalies.

Why do gamblers, sometimes get rich?


Adding a location key to a record completely eliminates any possibility of duplication.
You obviously do not understand how GUIDs are created and so I will stop trying to get you to see that they are completely safe for you to use. You seem to think that using your method, nothing can go wrong. Well, I hate to break the news to you - ANYTHING CAN BREAK OR GO WRONG. The odds of you getting killed by a car in the next 2 hours is way less than you have with using a GUID. But go your own way. Have fun.

SOS Out
 
Thales,
Your method will require you to use a table to control the generation of sequence numbers. Once you delete all the local rows, you won't have a reference to what was the last sequence number you generated. The key table could be:
tblKeySeeds
TableName (primary key)
LastIssuedNum
UpdateDate
 
Hello Pat,
We implemented a single table count method last year, but have since decided on another schema. For exactly the reasons you stated no way to reconstruct a concatenated key. Our (hopefully) final scheme will be to store the autonumber and local code in every record. Next a function will create a concatenated primary text key prior to updating the record.

This is an example of our new textural primary keys.

00010001000000000016

That will probably hold up for a few days of production data entry.

SOS,
You could fill a large granite mine with the things I don’t understand, but math is not one of them. As far as probability is concerned, there is no accounting for cluster theory, yet it does exist. Maybe it’s a force at the center of the energy of the Universe, maybe it’s an anomaly in a set of electrons; I don’t know. But I do know this; if you build an algorithm that must create unique numbers, it’s infinitely more dependable than one that creates probabilities.
 
You could have created a composite key using a LocationID field (Long) and used the DMax() function, or a table with a bit of incrementing code as in Pat's idea, to give you the next ID (Long) for your current LocationID which would be linked to your current location (office).

(I've used both DMax() and an incrementing function over the years but prefer to use the function method in the Form_BeforeUpdate event)

An easy way to to implement this would be for each office to have a unique linked table with it's own LocationID in.

Two other fields in your tables would have been useful.

deleted (Yes/No) - to mark a record as no longer required. This avoids records magically reappearing from other databases.
dateStamp (Date/Time) - to mark when the record was last updated

So with a combination of LocationID, ID, deleted and dateStamp you could move your records around, and consolidate them to your hearts content.
 
Last edited:
You are incorrect. You most certainly can and it is when the Autonumber is set to be ReplicationID. There is a one in 2^128 chance of a duplcate. You could have one billion people input one billion records a year for 10 to 20 years and still not likely get a duplicate.


interesting observation. i have wondered about the possibility/likelihood of clashing replication ids before.

what happens if you do get one?
 
Or you could get 500 duplicates, chance is a funny thing. You have to take into account cluster theory and other anomalies.

Why do gamblers, sometimes get rich?.

You certainly won't get rich as a gambler.

You are incorrect. You most certainly can and it is when the Autonumber is set to be ReplicationID. There is a one in 2^128 chance of a duplcate. You could have one billion people input one billion records a year for 10 to 20 years and still not likely get a duplicate.


That is an understatement. 2^128 = 3.4E38


If everyone on the Earth was each entering a billion records a year, for the entire history of the Universe there would still be less than one in three billion chance of any duplicate.


7E9 * 1E9 * 13.8E9 ~ 1E29
 
Now that I have decided, for right or wrong, to use a concatenated text field; I have a technical question.

I have 143 tables with data in composite keys, and I want to concatenate them into one text field.

Syntax has always been my great downfall

Code:
[COLOR=black][FONT=Verdana]Private Sub Command0_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim rs As DAO.Recordset 'DAO Component[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim Mydb As Database[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim strTable As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim strAutoNumberID As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim strLoc As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim strTableID As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'>>>>>>>>>>>>>>Replace Field Names >>>>>>>>>>>>>>>>>>>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]strTable = Me.Text9   '|[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Set Mydb = DBEngine.Workspaces(0).Databases(0)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Set rs = Mydb.OpenRecordset(strTable, dbOpenDynaset)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            rs.MoveFirst[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]              Do Until rs.EOF[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                 rs.Edit[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] 'TABLE NAME ID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      strAutoNumberID = rs!BusinessPlanID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Do[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         If Len(strAutoNumberID) < 12 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]          strAutoNumberID = "0" & strAutoNumberID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      Loop Until Len(strAutoNumberID) >= 12[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'LOCATION ID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        strLoc = rs!BusinessPlanLocationID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Do[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        If Len(strLoc) < 4 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]          strLoc = "0" & strLoc[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Loop Until Len(strLoc) >= 4[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]             [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] 'PRIMARY TEXT ID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    rs!BusinessPlanTID = strLoc & strAutoNumberID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'----------------------------------[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  rs.Update[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  rs.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Loop[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  rs.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

This works great but What I would like to do is enter the field names into a text box.

Can one of you guys help me with that please?
 
interesting observation. i have wondered about the possibility/likelihood of clashing replication ids before.

what happens if you do get one?

It's a bit like crossing the streams in Ghost Busters; the end of everything, everywhere.
 

Users who are viewing this thread

Back
Top Bottom