Re: [SQL] Design Question - Mailing list pgsql-sql
From | Andy Lewis |
---|---|
Subject | Re: [SQL] Design Question |
Date | |
Msg-id | Pine.LNX.4.05.9911281456150.17727-100000@rns.roundnoon.com Whole thread Raw |
In response to | Re: [SQL] Design Question (De Moudt Walter <wdemoudt@planetinternet.be>) |
List | pgsql-sql |
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@appl-ecosys.com > > > > ************ >