First attempt at a Database design... Help a Newb with his Keys.

[jloor]

Can someone see any potential problems with this design, it is my very first attempt and I hope I understand the 3rd Normal form. Any feed back will be greatly appreciated.

Client Table
Client_ID {Primary Key}
Client_Name
Client_Location {Foriegn Key?}

Client Location Table
Client_Location {Primary Key}
State_ID {Foriegn Key?}
District Name

District Code Table
Client_Location {Primary Key [Composite?]}
District_Code_ID {What kinda key is this? Primary 2?}
District_Code

Building Code Table
District_Code_ID {Primary Key [Composite?]}
Building_Code_ID {What kinda key is this? Primary 2?}
Building_Code

Client Contact Table
Client_ID {Composite Key?}
Client_Address_ID {Primary Key?}
Contact_Name
Contact_Email
Contact_Phone
Contact_Fax

Client Address Table
Client_Address_ID {Primary Key}
Client_Address
State_ID {Composit Key?}
City
Zip

Mileage Table
Client_Address_ID {Primary Key?}
Round_Trip_Mileage

Remote Access Table
Client_ID {Primary Key?}
Client_IP
Client_gif_location


Hmmmm, as you can see I am still a lot confused on what kinda Keys there are... all I know is that I think all the tables relate to each other... Can some lend some advice or corrections. I tried to break down the tables to "Single Themed" based... or 3rd Normal Form.

Thanks to everyone who can lend a hand.

[r937]

one good way to do a preliminary design is to examine entity relationships and their cardinalities

for example, you appear to have clients, locations, contacts, and addresses

take these in pairs, examine the relationship in both directions, and see if they make sense

for example (i'm making this up) --

each client has exactly one location
each location can have one or more clients

~ this tells you location-client is one-to-many, and that there will be a foreign key from client to location

each location has exactly one address
each address has exactly one location

~ this tells you location and address are one-to-one and could share the same record

each person can be a contact for one or more clients
each client can have one or more people as contacts

~ this says client and contact are in a many-to-many-relationship, and as far as keys go, you will need an intermediate "intersection table" to be the child table in two one-to-many relationships

now obviously, i've just made up a bunch of crappy relationships that might not be right for you

the point is, only *you* can decide what the relationships are in your application -- it's quite difficult to "unravel" them from the keys that you have embedded in your tables

i mean, i could try, but what if you have erroneously placed IDs and foreign keys into tables that didn't need them?

one way to salvage what you have is to pull all the foreign keys out of your tables and see what you're left with -- should be an ID and one or more descriptive fields in each table

then your relationship analysis should tell you where the proper foreign keys go

without the analysis of what you consider to be allowable one-to-many and many-to-many relationships, we'd just be guessing...

[jloor]

Rudy, thank you for your insight.

There seems to be a couple ways to design a DB, some people follow a "Star Scheme," One2Many/Many2Many, or Normal Form... hopefully I figure it out... its one of those things that takes practice I gather.

J.Loor