Unanswered: Database of clients with multiple addresses
I am working on creating a database of local business (restaurants, retail, services) which will work similar to yellowbook.com or yellowpages.com. A user will type in the name of a business or category (such as pizza) and it will give them all local places meeting those requirements.
My question is how to set up the tables in the database specifically for those who have multiple locations. (We have about 6-10 McDonalds in my town.)
I was thinking of having a Client table that would hold the address, phone number, web site, hours, etc for each client. But I started of thinking of those places like McDonalds compared to the local places that only have 1 location. With each business there could be anywhere from 1 location to 10. If I create fields in the client table to hold all possible addresses it seems like the table would get a bit out of control and maybe not the most efficient way to set it up.
My first thought was to have 4 tables. The client table (holding all client data: addresses, phone numbers, etc), the category table (restaurant), sub-category table (fast food) and a table that ties them together holding the client id number, category number and sub-category number. Is there a more efficient way to do this?
many clients will be under different sub categories and categories such as Subway could go under fast food but also caterers. So I do need the linking table I think.
Do you have any suggestions on how to do the client table(s)? Having the client table hold a possible 10 locations seems a lot, but maybe it is the best way. Any thoughts on that? Thanks.
Also the reason I was thinking of breaking the category and sub-category into 2 tables was I am currently unsure how many of each I am going to create. It is basically a online phone book but focused on the local area and will have a local calendar and news added to it. So as I get clients to advertise on it I may add more categories or sub-categories to better fit them. Having it in 2 tables would be easier to edit I think.
I did think about doing it that way, but thought doing that might come back to bite me and make other things difficult later. I plan to have it where they can purchase 'extras' to their listing showing a menu, services or coupons. And I don't want to enter a menu 10 times. However there will probably be a menu table holding all the menus with just some sort of linking table so it only has to be listed once.
Mainly putting this out there to see what ideas other people come up with to make sure I create the database structure the right way the first time. I don't want to have to go back and re-enter all the businesses once they are in.
Yes, it is the field within the table that is guaranteed to be unique. Such as in the client table the client id will be set as the primary key to keep there from being more than one client with the same id.
I can't decide if you are asking that question because you obviously do not know how my table is set up or if you are trying to have me give you the correct answer by thinking on my own. Is this a test? lol
The address is the only other field that would make sense to make the primary key. However, my only concern is having several business with the same address but different suite numbers/letters. I am filling the database with a directory of business I bought from the phone book company so if they do not have suite numbers in document they are sending me that could cause a problem. So I think I will be making the client id unique.
I can't decide if you are asking that question because you obviously do not know how my table is set up or if you are trying to have me give you the correct answer by thinking on my own. Is this a test? lol.
Answers are worthless [to you] if you don't understand them!
Give it your best guess, the worst thing you could do is be wrong.
Excuse me r937, but why not using a separate table for addresses that has 3 fields: adrs_id , address , res_id , and listing all the address for all restaurants and giving the restaurant's id in the restaurant table ? that way he could use a simple join to get a restaurant's name and all the addresses corresponding to it.
Its just an idea, and I would be happy if you find a bug in it since I'm so convinced with it right now