Re: Database Schema options....Need some advice

slomu_at_onebox.com (jonah) wrote in message news:<18bd71da.0201081950.cf651c7_at_posting.google.com>...
> If this isn't that clear let me start off by saying sorry!
>
> I'm developing a database driven website and this is the first time
> I'm doing it from scratch and alone so I could use a little input.
>
> The specific question I have is about linking tables in MS Access 97.
> If I have a table named Scheduale and it has a field in it named
> Location which is linked to a Locations table which has an ID and
> Location field and the Location field can be either Home, Away, Tour,
> etc..
>
> Like So:
> ----------- -----------
> |Scheduale| |Locations|
> |---------| |---------|
> |ID | |ID |
> |Location |---------|Location |
> |---------| |---------|
>
> No when I link the Scheduale.Location field should I link it to
> Locations.ID or Locations.Location? What are the pros and cons of
> each? I understand linking it to the ID will just give me numbers and
> I'll have to consetently keep looking up wich number is for which
> value but I would think this will make data checking easier. Any Help
> will be greatly appreceiated.
>
> Jonah

Hi,

that's the right way!
This solution will let you act much more flexible than hard-coded stuff. So you can change the Location-Description as you like, without getting any problems with joins and querys - while you're developing joins using the primary-key <- foreignkey-constraint.

Anyway, it's not a good style to create information redundantly - therefore the terminus "normalization" accompanies with database theory.
If you build this constraints, it will approve performance too; cause to slow String comparisons (better use integer).

Try this way:

• ----------- |Scheduale | |Locations| |-----------| |---------| |ID | +----|ID | |Location_ID|----+ 1 |Location | |-----------| n |---------|