Database Design Question

[marvinds]

I have a question about the database design for my project. Originally I was going to have separate tables for Managers, Owners, Players, etc... since they all require different information. This design created a problem on how to add phone numbers. In order to do this, I was going to have to create separate tables for Managers_phone, Owner_phone, Player_phone since you couldn't have a foreign key in one phone table relate to different tables.

 

Could you just create one table for People, with all of the information that they have in common such as first name, last name, address, etc..., and link phone table to this table, and then have seperate Manager, Owner, player tables to store the information that is unique to each?

 

Also, I am planning on just storing the zip code on the people table, and then have a separate table that matches each zip code to the proper city. Is this fairly common, or do you usually store City, State, and zip in the same table?

 

 

[HartleySan]

For your phone number table, you could have a foreign key that is a combination of two columns; a column that IDs the appropriate user-type table (i.e., managers, owners, players), and another column that IDs the actual ID in that table. The combination of those two columns would always (or should always) be unique, and thus would solve your problem.

 

What do you think?

 

As for storing just the zip code versus all the city data, that's a good question, and I don't really have a right answer for you.

In my experience, if you're going to want a table of city/state/zip code info, you're likely going to have to get the table from someone else/some company that has taken the time to create it all, and that keeps it up to date all the time.

As such, you'll likely be limited to whatever they provide, which in my experience, has always been one massive table that contains a lot of zip codes, city names and state abbreviations.

 

If you have any other thoughts/questions, please let us know.

Thanks.

[marvinds]

I do think that could work with the phone numbers. I am new to both database design, and mysql, so I'm not sure how it would be done, but I'm sure I can figure it out.

 

As for the zip codes, my plan was just to build the table as users enter their information. When they enter their city, state and zip, I would check it against the database to see if the zip code existed. If not, I would add it to the zip code/city table. If it did exist, I would check to see if it matched the city that was currently in the database. If it didn't match I'm not sure what i would do, but maybe create a temp table until I could determine the correct zip/city combination. Would hope this wouldn't be common and users would be entering the correct city/zip combo most of the time.

[HartleySan]

For the phone number table, I'd attempt what I recommended.

For the zip code, I'd definitely use some existing table out there, as populating all of that yourself is not something that you want to waste your time on.

[Larry]

Personally, I've never done a separate city/state/zip code table myself. I've always stored all with the user's address information. The only exception I'd make is if the customer were to provide a zip and you'd do a city/state lookup. In that case, I'd want a commercial table like HartleySan said.