Re: [SQL] Design Question

Yes, thanks!

Andy

On Sun, 28 Nov 1999, De Moudt Walter wrote:

> Hi Rich,
>
> I think that if you :
> - create one table "brokers" with the broker's name (let's call that
> broker_name) and a unique id (just in case two show up with the same
> name, there are many Smiths, isn't it ?). The ID could be an integer
> up-counting number (1 for the first, 2 for the second, ...), let's call
> that field broker_id.
>
> -create a second table "locations" which contains all nessecary fields
> for a certain location of a certain broker (street, mailbox, city,
> state, ..) AND a field broker_id, which should have the same integer as
> the broker_id of the broker's table.
>
> Now the name of the broker only appears once in the brokers table. So
> even if the broker's company should be called "John Smiths Absolutely
> Marvellous and undoubtedly honest stock trade company", and have 50
> houses all over the states, this name only takes disk space once. And
> that's one of the objectives of relational databases : limit the
> occurance of extensive and repeating data by writing it once, and then
> pointing to it, instead of writing it again and again.
>
> You should take care, however, that the datatype for broker_id is the
> same in both tables ! So, if you use an integer for it in the brokers
> table, then use an integer type in the locations table as well !
>
> Hope this explains something :-) It's only the tip of the veil, of
> course. If this interrests you, you should really buy a book on
> relational databases.
>
> Toi,
> Walter De Moudt
> Rich Shepard wrote:
> >
> > On Sat, 27 Nov 1999, Andy Lewis wrote:
> >
> > > Lets say that I have a DB that I am using to save address info on
> > > different brokers. Broker name, address, city, state, zip
> > >
> > > Some brokers have representation in different states.
> > >
> > > How would I be able to save that info so that it can be later selected by
> > > users looking for a broker in a certain state(s)?
> > >
> > > What type of field/table/DB could I save that in for easy reference?
> >
> > Andy,
> >
> > I suggest that you buy (or borrow from a library) a book on relational
> > data base design. To give you the simple answer to your question: have one
> > table for the broker's name and identification and a separate table for
> > their addresses. It's called a many-to-one structure and is created by
> > normalizing the data.
> >
> > Rich
> >
> > Dr. Richard B. Shepard, President
> >
> > Applied Ecosystem Services, Inc. (TM)
> > Making environmentally-responsible mining happen. (SM)
> > --------------------------------
> > 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
> > + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard(at)appl-ecosys(dot)com
> >
> > ************
>