Wanted: DB Design suggestion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Wanted: DB Design suggestion

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-04-27 : 17:40:28

I'm having a little struggle here with getting my database normalized.

Here's a sample report I want to store in a database.

Name Company Address Dates
John ABCComp 24 High St, LA, CA, USA 4/04 - 12/04
John ABCComp 12 LOw Av, SF, CA, USA 1/03 - 3/04
Jane XYZComp 129 Hill Blvd, SLC, UT, USA 7/04 - 9/04
Jane XYZComp 12A River St, Nice, FRA 10/04 - 12/04
Jane XYZ Comp 12A River St, Nice, FRA 4/04 - 6/04
Jane XYXComp 5ABC Calle, Guadaljara, MEX 1/04 - 3/04
Mary DEFComp 4212 Van Av, Toronto, ON, CAN 6/04 - 8/04
Mary DEFComp 111 Done Cir, Calgary, AB, CAN 1/04 - 5/04
Mary DEFComp 111 Done Cir, Calgary, AB, CAN 9/04 - 12/04

I think I should have a ContactInfo table where name and company will be. Other tables I don't know how to split the address and dates to get to the 3NF.

Pls help. Thanks.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-27 : 17:58:47

Is this homework?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

PeterG
Posting Yak Master

156 Posts

Posted - 2004-04-27 : 18:02:33

I'm a newbie sql developer and I've read some references on the Net and just can't seem to get this right. The DB should be able to tell me where to send bills to a client at a particular date.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-27 : 18:44:22

Well, I think you need three tables

1. Table to Store Company Name
2. Table to Store Many addresess for each company
3. Table to Store Valid dates for each address

Table 3
CustomerID (You don't HAVE to have this, but I suspect it will make your queries faster)
AddressID
Valid To
Valid From

You can put some CONSTRAINTs on that third table to make sure that two addresses for a single customer are not valid at the same time.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources