Re: Schema Anomalies

I think The "implied relationships" is somehat trickier to check.

Take for example:

Customer:
CustomerID
Billing2AddressID
Shipping2AddressID

Address:
AddresID
City
Zip
etc....

Currently your setup only checks for Natural joins (where foreign key
column names equal the primary key column names). This is OK.

But this is only the simple version of implied relationships. In a
database where multplie roles are used this is never going to
happen(esp. when the naming convention takes this into consideration
for all foreign keys!). For good naming conventions however it is
imperative that the names of the primary key column names also show up
on the foreign key names, or that the entity of the primary key shows
up on the foreign key name. The last option is probably the rarest.
I know checking all options is going to be tricky!
I would say you would (Optionally) check if a column name starts or
ends with a column name of a primary key would be a good heuristic
choice. A good naming convention will stick to this approach in one way
or another. I think that is about the most you can do to find implied
relationships.

I know of databases that actually have no SQL relations defined "by
design", beacuse they would only mean extra overhead on the database
server. While the database will not enforce referential integrity this
way, you can still have a database without any referential anomaly.

The discussions on single culumn tables (AKA lookup tables) goes long
and wide. From the (fundamental) design side, the more tables the
better (since tables are linked to semantics of the model). From
programming side the less tables the better. The truth lies always
somewhere in between.

A good rule of the thumb I use is this:
If I lump 2 lookup tables together, and values get mixed up(used
illegally) inside the database, how bad is it?
catastrophic? These 2 lookup tables should be distinct
problematic? These 2 lookup tables should be distinct
nuisance? These 2 lookup tables could be joined into a larger table
Using this rule you usually end up with a load of small but important
lookup tables, and one big lookup table with all the rest of the
lookups.
Another way to look at it is the following:
Is the contents of the lookup table owned/used by the
system/application/program in a very specific way, then it needs it's
own table.
Is the contents of the lookup table owned/changable by the user and of
no consequence to the system, you can stick it in one big lookup table.

there are globally 2 ways to look at relations:

symmetric:

Table1:
OTM (Mandatory) Table2
(Optional) OTOP Table3

Table2:
(Mandatory) MTO Table1

Table3:
OTOF (Optional) Table1

Assymatric:

Relations:

Relation1: Table1 OTM (Mandatory) Table2
Rerlation2: Table1 (Optional) OTOP Table3

You can check One To One relationship based on unique index/contsraint
on foreign key. Also Mandatory = Foreign Key Not NULL, Optional =
Foreign Key NULL,

Mmm, the intersection/junction table terminology is more of a flavor,
but maybe junction table would be more accepted.
Note that a binary junction table = (binary)MTM relationship.
If it is not that difficult, maybe also recognize a primary key on an
intersection table? This is also a bad habit of some database designers
that could stand a check.

DM Unseen

.

Relevant Pages

  • Re: FAQ? factors influencing choice of data type for primary key
    ... >>example, an Autonumber as a primary key, are there ... >>it is a foreign key? ... >don't need to bring the lookup table or any indexes into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Rule when primary key is needed?
    ... When you create a table that is part of a "relational" database. ... If you are talking about lookup values for a main table then you should ... part of the normalisation process is to choose the primary key for the data ... normalisation which can only cause you trouble in the long run. ...
    (microsoft.public.access.gettingstarted)
  • Re: Combo Box AfterUpdate Help
    ... Compound/Composite Primary Key and you need to create a Compound/Composite ... Foreign Key in the related table, ... > database design I would have asked for that instead. ...
    (microsoft.public.access.tablesdbdesign)
  • Database Table Maintanance by Text File
    ... I want to update a database table by a text file. ... all no primary key fields in the database with the corresponding fields of the text file. ... A normal data pump task; ... Or, do I need to use a data driven query, maybe with a lookup? ...
    (microsoft.public.sqlserver.dts)
  • Re: Lookup Tables, the right way ?
    ... If you are defining a database table that requires a lookup table, ... then the foreign key between the two tables should be a character ...
    (comp.databases.theory)