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?

definitely, yes

you should collapse the category and subcategory tables into one

see Categories and Subcategories

as for the linking table, this would only be needed if you are going to allow any location to belong to more than one category or subcategory

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.

Edit:
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.

simple: each location is a different client

the fact that there are 10 mcdonalds locations should mean nothing

look at how the phone book is organized

they don't make a special type of listing just because 10 guys named johnson all have phones

you don't need to make a special type of structure in your tables for franchise locations unless there is some data that exists only at the group level


by the way, have you ever wondered why there are actually so many johnsons in the phone book?






they all have phones!!

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.

after you enter them once, you would never need to do it again, no matter how many times you redesign your structures

a business is a business is a business

do you know about primary keys and what they're for? note: i'm not talking about auto_increments

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.

yes, that's true, but if you make that client id an auto_increment, then of course each client's id is unique -- but this wouldn't stop you from entering the exact same data more than once

the most common question posted on database forums is "oh noes!!1!! my table haz duplicates!! how can i delete all but one of them????"

do you know how to declare a primary key that isn't an auto_increment?

yes, with phpAdmin it is basically just clicking a button. is there a field you are thinking would be best to make the primary key?

that is up to you

disregarding the auto_increment, what columns do you have that would be unique for every client/location?

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.

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.
Spoken (and punctuated) like a true pro

i was trying to get you to think, and you gave up too early

unique would be address plus business name

how likely is it that you would have two businesses with the same name at the same address? (and if you say that it is possible, i will say that they are the same business!)

if you are loading external data, and are planning to use an auto_increment, you'd better think about this carefully

well the problem was in the fact that I did not know you could have to unique fields. I thought you could only set one as the primary.

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

Forgive the bad english