Help/advice with Foriegn keys

From: Dave Stratford <daves_at_orpheusmail.co.uk>
Date: Fri, 15 Apr 2016 19:02:32 +0100
Message-ID: <55714ee1dadaves_at_orpheusmail.co.uk>



Hi folks,

I'm doing some initial design work on a small (hopefully fairly simple) reference database.

In one small part of the DB I've got a table, called for the sake of convenience here, ENTITIES.

Table entities has key IIN smallint(3) - there shouldn't be more than about 120 entries and as it's a reference table in a reference database it's unlikely to ever change, or even grow. If anything it's actually likely to shrink.

Entities also has a field name char(30), and here is where I need a bit of advice/assistance. Most entities are also referenced by a short form, synonym, and a few have a number of synonyms. I've found one with 8 synonyms but most only have 1 or sometimes 2.

Question.

Should I have a separate table just for the synonyms, or a repeating element that on most lines won't be used.

Assuming a separate synonyms table, it would have the IIN, which would link back to the entities table, and a key of the synonym. My initial design has the synonym as the key (most are no more than 5 characters, and the longest I've found so far is 13) and the IIN as a foreign key linking back to the master entities table.

Is this correct? How would I set up the foreign key?

IIN on the entities table is itself a foriegn key to other parts of the DB.

I've never used foreign keys before - not in my own designs - so any comments and help on how best to define and set them up would be most gratefully received.

Many thanks,

Dave

-- 
Dave Stratford - ZFCB
http://daves.orpheusweb.co.uk/
Received on Fri Apr 15 2016 - 20:02:32 CEST

Original text of this message