Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Question 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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    7
    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.
    Last edited by ryanfc; 04-29-08 at 17:23.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2008
    Posts
    7
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ryanfc
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2008
    Posts
    7
    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.
    Last edited by ryanfc; 04-29-08 at 18:21.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2008
    Posts
    7
    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?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is up to you

    disregarding the auto_increment, what columns do you have that would be unique for every client/location?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2008
    Posts
    7
    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.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ryanfc
    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.
    Quote Originally Posted by r937
    oh noes!!1!!
    Spoken (and punctuated) like a true pro
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2008
    Posts
    7
    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.

  15. #15
    Join Date
    May 2008
    Posts
    9
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •