SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design advice

    I got some intitial advice on the PHP forum but still I need some assistance getting my database design correct.

    Basically I am creating a directory of Accommodation, Pubs and Restaurants and Attractions.

    I currently have 3 separate main tables:

    accommodation
    pubsAndPlacesToEat
    attractions

    There are some common fields in each of these tables plus some fields that are only relevant to each table.

    The common fields are as follows:

    businessName
    businessType
    businessAddress
    businessTelephone
    businessWebsite
    businessDescription
    businessDirections

    Examples of differing fields are as follows:

    accommodation
    ----------------
    roomAmenities

    pubsAndPlacesToEat
    ---------------------
    openingTimes
    menu

    attractions
    -----------
    prices

    I would like to know if it makes sense to just have one main table say called "businesses" and include all the similar information fields in this?

    If I do this then I am not sure how I would deal with the information that differs between business types. I need to consider this both from an admin perspective (ie how the admin page will be able to update all the info in the database for a particular business listing) and also from a search perspective.

    Any advice much appreciated.

    Thanks

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2008
    Location
    NC
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like you need an intro to normalization. Here's a very good one from MySQL:
    http://dev.mysql.com/tech-resources/...alization.html
    Basically, you don't want to repeat information across tables. So from that standpoint, I might create one table called businesses, with all of the common information, a table called amenities, listing all possible amenities, and a "bridge" table that relates each amenity to a single business. You can carry that logic to the other tables as well.

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanka ditch

    The idea of one generic table to store all the generic business information sounds great.

    If I have a table called amenities, is it possible for this table to include both groups of checkboxes and groups of radio buttons. If so how? Perhaps I need to break it down into even more tables?

    For example

    Checkbox group:

    What entertainment applicances are included in room:
    Checkbox 1: TV
    Checkbox 2: Radio
    Checkbox 3: DVD player

    Radio group

    How many pets allowed in room?

    Radio 1: None
    Radio 2: One
    Radio 3: Two
    Radio 4: More than two

    I will read the article on normalization now.

    Thanks

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds like a classic supertype/subtype structure to me

    if you search through a few articles on that topic, you'll find a whole bunch of ways to create the tables

    however...

    based on the number of columns if similar supertype data, and the number of columns of unique subtype data, my advice here would be to have just one single table, containing all columns

    add a "business type" column to distinguish whether it's for sleeping, eating, or playing, or whatever codes/words you want to use, and then use NULLs in the columns that don't apply
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First up - great book! I have read the free chapters and am going to buy the full version.

    You suggest I just use one table. I think this would definitely keep things simpler for searching.

    However whilst this sounds ok from the perspective of listing the business details, I was hoping to dynamically generate the amenities and have an admin page for myself to add more amenities if I decide I need additional ones. I would also like to have the amenities grouped.

    So basically my admin form to edit/create a listing would have the first lot of details hard coded i.e name, address, telephone etc. Then I would dynamically generate the radio button and checkbox groups from the database. It is how I store the details of these groups in the database that is confusing me.

    Currently I have 3 separate admin forms, one to create/edit each of the following:

    accommodation
    eating/drinking
    attraction

    Each form has an identical first section that has items such as business name, address, telephone, email etc

    I was then going to hard code the amenities for each form but then realised I need to be able to add more amenities via an admin page.

    So if I want to dynamically generate these groups of checkboxes and radio buttons for the amenities, I would appreciate your advice on how best to store these. For both radio button groups and checkbox groups there can be anything from 1 to 20 checkboxes/radio buttons in each group. I would ideally like to group these groups into categories to. So I need to store the following for each group of radio/checkboxes:

    1. Which business type (or types) this applies to (e.g Hotel or Pub or Adventure Park)
    2. Which sub group this applies to (e.g Room facilities, Pet Policy etc)
    3. The group title (e.g What entertainment appliances included)
    4. The group options (e.g TV, DVD, Radio etc)

    I am finding this difficult to work out - I want to be sure I don't create something really inefficient for searching.

    Any help appreciated.

    Thanks

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you'll need the many-to-many structure between businesses and amenities that ditch suggested in post #2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy.

    Ok if I create a separate table for my amenities, would the following structure be ok in order for it to be able to save both radio button groups and checkbox groups?

    id: smallInt
    inputType: varChar ("radio" or "checkbox")
    amenityTitle: varChar
    option1: varChar
    option2: varChar
    option3: varChar
    option4: varChar
    option5: varChar

    This assumes that no group would have more than 5 options.

    How does this look as a table structure?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    not quite. i suggest something more along the lines of this:
    Code:
    create table businesses (
       id int unsigned auto_increment primary key
     , name varchar(50) not null
     , type varchar(50) not null
     , coffeemakers smallint unsigned null
     , kingrooms int unsigned null
     , doublerooms int unsigned null
     , lanes smallint unsigned null
    );
    
    insert business (name, type, coffeemakers)
    values ('starbucks', 'coffee shop', 3)
    , ('blue moon', 'coffee shop', 2);
    
    insert business (name, type, kingrooms, doublerooms)
    values ('hyatt', 'hotel', 35, 125)
    , ('quality' , 'hotel', 15, 80);
    
    insert business (name, type, lanes)
    values ('tampa lanes', 'bowling alley', 40);
    so just leave the columns you're not using for a particular row as NULL.
    Last edited by longneck; Mar 16, 2009 at 05:33.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2008
    Location
    NC
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Chucky,
    Try searching for "many to many relationships mysql" in order to get a better understanding. Basically you can create your amenities table using just one column:
    amenityTitle: char

    Then you'll create another table, call it businessToAmenity. That table should look like this:
    id:int
    business_id:int(This is the primary key from the business table)
    amenity_title:char(This is the primary key from the amenity table)

    That table now outlines the relationship between amenities & businesses. Radio buttons & checkboxes are a bit of a red herring here. Just make the value of each button or box be the amenity title in your html form. Then when you process the form submission, you can use a foreach construct to add a record to the businessToAmenity table for each value that is checked.

    Rudy, awesome book, and it's a pleasure to even be in the same thread with you!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    thanks for the kind words, ditch

    may i ask you to write a review? when you're ready, of course
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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