Mike: Glad you brought this up. This is a subject where programmers
are split on how to handle this. One of the consultants I work with has a
separate table for every type of lookup. These tables frequently have less
than 10 rows, and his database can have up 30 of these tables. But I'm
like you -- I have ONE lookup table (usually call it "Lookups") something
like this:
LookupType (which has a short description of the type of lookup,
and my where clause would be "where LookupType = 'StatusType' ")
LookupValue (which will have all the values)
LookupSeq (if the menus/lists will sort other than alphabetically by
LookupValue)
LookupID (autonum, the integer id that is assigned to the referring table)
You can put a Unique constraint on LookupType / LookupValue. You can also
set up a PK/FK relationship between the LookupID and each table that
references this table. Other than potentially a PK, I do not index.
I don't go crazy with PK/FK assignments. For example, if you have a lookup
table
of order statuses (In work,Completed,Cancelled) we can assume this table would
probably never change. The only real issue is if you deleted a referenced
lookup value.
But status changes usually mean you'd also be adding programming logic to deal
with
new statuses or statuses that are dropped. And you would NEVER let a user
edit
the data in an order status lookup table. So why bother with a PK/FK
relationship
if no one other than a programmer will be monkeying around with the statuses?
And
putting an FK on the referring table creates an extra piece of maintenance on a
table
and a column that probably doesn't have many unique values.
Frank Taylor and I just got through with a project to drop a bunch of these
PK/FK
relationships for tables where the user never touches the PK lookup field.
This is a huge
database and we decided to do a major KISS campaign, dropping PK/FK that
weren't necessary, dropping indexes on columns that didn't have enough
unique entries, etc. Well worth the time spent!
Happy Memorial Day to all the Americans out there. I spent yesterday helping
village employees put flags on the veterans' graves in our 3 cemeteries. It's
a little
thing to do to remember the world's true heroes.
Karen
-----Original Message-----
From: Mike Byerley <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Sat, May 25, 2013 6:41 am
Subject: [RBASE-L] - Re: Primary key
While you are at it, "numerous tables" strikes a chord with me as being
unnecessary. I use a single table structure like:
CREATE TABLE `MenuTable` +
(`MenuName` TEXT (12) NOT NULL , +
`MenuSeq` INTEGER NOT NULL , +
`MenuText` TEXT (50) NOT NULL , +
`ReturnValue` TEXT (16) NOT NULL )
COMMENT ON TABLE `MenuTable` IS +
'S - Various Menu TEXT and Return Values'
You can see it is not indexed at all as it only contains a few hundred rows,
but if it ever became noticibly sluggish, I would put an index on the
menuname.
MenuSeq was added after a year or so, when I wanted something at the top
selection that OrderBy wouldn't provide. All return values are TEXT, and
when the result needs to be otherwise, the values are converted at runtime.
----- Original Message -----
From: "TOM HART" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Friday, May 24, 2013 11:10 PM
Subject: [RBASE-L] - Primary key
I am in the process of redoing my database that I've used for over 10 years,
just trying to use logic in naming, correct syntax, etc. I have numerous
tables that hold data to use to fill fields from popup menus and just wanted
to know if it is better to set the field as a PK or unique, they do not
reference other tables so there are no foreign keys. Is one way better that
the other, just curious
Tom Hart
