Re: Help/advice with Foriegn keys

On 4/15/2016 2:02 PM, Dave Stratford wrote:
> 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,

So if I understand this correctly, your ENTITIES table has an ID and a name. You also have zero or more synonyms for the entity, i.e. you might have

1, 'Boat' as an entity, but you also may have synonyms of 'ship', 'yacht', etc.

The question here is - how is the data going to be used? If someone enters 'yacht', do you want to use 'yacht' or do you want to use 'boat'?  IOW, do you want to use the synonym or the entity name from there on? That will determine how you design your tables. If you are going to use 'boat' from there on, a separate synonyms table would be appropriate. But if you want to keep the term 'yacht', you should have one table, with 'boat' and 'yacht' having separate keys.

Proper table design is as much about data usage as the data itself.

And there really isn't a problem with using a character column as an index (a key is just a special form of an index). It's slower than using an integer type, but generally not noticeable until you get into longer keys and larger databases.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================