Re: Help/advice with Foriegn keys

In article <nercn2$o1s$1_at_jstuckle.eternal-september.org>,

   Jerry Stuckle <jstucklex_at_attglobal.net> wrote:
> 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.

That's correct yes.

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

On data entry, either the entity name or a synonym might be used. On reporting the main name will always be output.

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

That's sort of what I hoped.

Thanks,

Dave

-- 
Dave Stratford - ZFCB
http://daves.orpheusweb.co.uk/