Simple relationship table design, Why ID numbers? (1 Viewer)

Hi,

I have a database with no relationships and it serves its purpose, but it would probably give experienced database programmers a heart attack I'm looking for the first time personally at introducing relationships for referential integrity (which has to be all considered and performed manually right now) and I have a question.

I have a list of 'Interleaves', within this list I would like to classify each Interleave into one of a handleful of 'Interleave Types'. I want to distinguish between these Interleave Types because in some forms I want to use comboboxes where some will list only one Type of Interleave, whilst other comboboxes will list all Interleaves of all Types.

Having researched, I *think* I should be doing this with two tables and creating a relationship between them. The examples I have come across would have me create the following:

Table: Interleaves
InterleaveID (PK)
InterleaveName
InterleaveWidth
InterleaveTypeID

Table: InterleaveTypes
InterleaveTypeID (PK)
InterleaveTypeName

with the relationship being on the InterleaveTypeID (and I assume a Many to One relationship from Interleaves to InterleaveTypes?).

But I am thinking if Interleaves.InterleaveName and InterleaveTypes.InterleaveTypeName are always unique, can I drop the autonumbering column altogether and have say:

Table: Interleaves
InterleaveName(PK)
InterleaveWidth
InterleaveTypeName


Table: InterleaveTypes
InterleaveTypeName(PK)

Its just all the examples I see, there is ussually this autonumbering primary key field, and I very rarely see a single field table. That makes me think I am making poor design choices. But is what I propose acceptable?

Thanks for any advice
Batfink

Hi,

My view is you should keep the autonumber ID field. Its best practice and will make it easier for you if you link from ID to ID rather than Names.

Your relationship should go from Table Interleaves column InterleaveTypeID to Table InterleaveTypes column InterleaveTypeID.

Remember to enforce referential integrity and cascade/delete etc...

I would say you are making a big mistake if you ditch the ID columns.

Hey thanks very much for the advice. I'll stick to the autonumbering ID fields then!

I'll start playing around in a new database and see where it leads me

Just to clarify, the relationship I am looking at is a Many to One (Inner join) type relationship? Because there are many records in Table Interleaves but they will only ever relate to one record in Table InterleaveTypes?

Thanks again.

Yes I think so.

What you want from your query is for it to pull out all Interleaves fields and their InterleaveType. So many to 1 sounds right.

thebatfink said:

Its just all the examples I see, there is ussually this autonumbering primary key field, and I very rarely see a single field table. That makes me think I am making poor design choices. But is what I propose acceptable?

Click to expand...

Yes, it's perfectly acceptable. In principle referential integrity constraints can reference any candidate key in the parent table. Technically, in many DBMSs it doesn't even have to be a candidate key but you should generally ensure that it is: i.e. it should be a minimal superkey ("minimal" meaning you can't remove any attribute from it and still satisfy the requirement for uniqueness) and uniqueness and non-nullability should be enforced.

There are three other criteria that it's sensible to apply when choosing and designing keys however: Stability, Simplicity, Familiarity. Familiarity means the users can recognise the key values and will understand what they represent. Simplicity means the key or its components will be concise enough to be a useful as an identifier. If the data that makes up a key is too complex or prone to formatting or spelling mistakes then users may get it wrong when reading or entering data). Stability means the key values don't change very often.

The main reason usually given for using a surrogate key (meaningless, auto-assigned values) is to minimise problems sometimes associated with changing key values, especially for keys which are referenced by foreign key constraints in other tables. Unfortunately the significance of those "problems" is sometimes exaggerated by rumour and ignorance. In many cases key updates are a perfectly normal and reasonable thing to do. Not every key will be referenced in other tables and even surrogate keys sometimes need to be updated. So deciding whether to use a surrogate is really a subjective and relative judgement that ought to be made on a case by case basis.

You can decide for yourself how well InterleaveTypeName is likely to fit the above criteria. If you do use a surrogate key then make sure you still enforce the uniqueness of InterleaveTypeName.

It is rare that you come across a natural key that is short enough and stable enough to use as a PK and something with "name" in its name doesn't sound like it would fit the bill so I would probably go for a surrogate key but define a unique index on the "name" field to prevent duplicates.