Re: Help/advice with Foriegn keys

On 04/15/2016 08: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.

If you follow the database normalization you would split it out, also I would say it's easier on the coding level too to have the short names in their own table.

> 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.

As long as you use a database engine which supports foreign keys like innodb.

> 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.

If the IIN is from another table, then make that table the source for the foreign key, at least IMHO it's best.

> 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.

You usually set them up when you create the tables, but of course you can alter table later and add it, just see to that the table do have allowed values in the column which you are going to make into a foreign key or else the alter table will fail.

https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html

-- 

 //Aho