Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Database Schema options....Need some advice

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

From: Slarti <1012-873_at_gmx.de>
Date: 10 Jan 2002 02:13:52 -0800
Message-ID: <ccb8e3b6.0201100213.5ded80f7@posting.google.com>


slomu_at_onebox.com (jonah) wrote in message news:<18bd71da.0201091438.3cad5f16_at_posting.google.com>...
> Thanks for your input....really appreciate it.
>
> One thing what do you mean by "while you're developing
> joins using the primary-key <- foreignkey-constraint."....I'm not sure
> I quit follow.
>
> Thanks again!
>
>
> 1012-873_at_gmx.de (Slarti) wrote in message news:<ccb8e3b6.0201090502.783d5f81_at_posting.google.com>...
> > 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 |---------|

...sorry for not finding the proper words. Well, if you will use this technique accurately, it'll be good coding style in your querys respective really good database design. Good as far as you are flexible enough to modify design, scripts or data (during development process - development and changes belongs together) without the need to change entire source code or data.

A database constraint in this case means, the relationship between Primary Key (parent) Locations.ID <table.attribute> and Foreign Key Scheduale.Location_ID (child) - so you can have n child records refering to 1 parent record (n..1).
But who am i telling?

If you remember this fact and code with this knowledge, you will get problems also - different, but not that kind; because you thought about that upcoming trouble during design process ;-)

That's all i meant.

You're welcome! Received on Thu Jan 10 2002 - 04:13:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US