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: who me <snooze241_at_hotmail.com>
Date: 20 Jan 2002 19:48:33 -0800
Message-ID: <a05c28e0.0201201948.592022a4@posting.google.com>


I'll make it easy on you. Instead of having the ID in the locations table the primary key, drop it and make the location a primary key. That is the simplest but not the best way to do this. The 1:N relationship is correct and still stable.

That solves the problem of having to look-up the ID number. Remember, a primary key is just unique, not necessarily numeric.

Eric

1012-873_at_gmx.de (Slarti) wrote in message news:<ccb8e3b6.0201100213.5ded80f7_at_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 Sun Jan 20 2002 - 21:48:33 CST

Original text of this message

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