DB Question

[OogyWaWa]

So, at a previous employer I was taught about reference tables. The way I understood it, was basically a DB can search ints faster than chars/varchars so you give textual data (char/varchar) unique values and a table which references it. Ex)

Table Flags
Flag ReferenceCode
1 1123

Table ReferenceCode
ReferenceCode Name Description
1123 TurnOnFeature Set the flag to 1 to turn on the feature


So, basically you can do all of your checks, joins, etc. on numbers instead of text. Then, if you ever actually need the text, you can reference it with the code.

Anywho, it seems like a pretty descent idea and it worked for the company, but is there not some DBMS that can automate this? I mean, the team I worked with had to managed 1000s of reference codes and if you ever had duplicates it really f'd things up. Since, in reality, the reference code is an arbitrary number, you would think this could be automated & managed, no?

Will someone tell me if I've totally gone awry or if this makes sense to you too...

 

[degibson]

What you're talking about here is a very common database practice of using a numeric key. A good combination of schema (table design) and client-side application can often keep the keys totally invisible -- e.g., users never see them, never enter them, keys are always unique, etc. That would be the best way to automate them. To be clear, in your example 'Flag' is the key for table flags, 'ReferenceCode' is the key for table referencecode, and 'ReferenceCode' is a foreign key in table flags.

I'm not sure a DBMS can safely automate key generation and keep them transparent -- it is often the case that the key is the only unique element in a record, which means they need to be scanned, or kept in yet another table.

High level point is:
-> Need to organize both your client and your schema to make this problem go away.

 

[Ka0t1x]

Am I wrong or is this just common database normalization? PK/FK.

MySQL has auto_increment column property, Oracle has sequences. Both of which should be unique based on table.

In the case of duplicates using a join table.. You can setup a constraint with a compound primary key (which will not allow duplicates of the column combination).