Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Unanswered: Multiple records in one cell

    This is my firs message in forum so hi to everybody
    I need idea about my database structure
    Here is my table named “Hfacilities”

    Fid|Facility
    1 |S Pool
    2 |Parking
    . |.
    n |n

    And table named “hotels”

    Hid|hName|hFacilities
    1 |Test |1,4,6

    This is the first idea of my that splitting hFacilities cell and getting values from Hfacilities table.i think this is not true way.Also one of my idea is creating one more table like this

    ID|fid|hid
    1 |2 |1
    2 |3 |1
    3 |4 |1

    But i think it can be create a lot of data because every hotel have 10-20 facilities and there are more than 300 Hotel.can it be problem?
    So i am very confused, can anybody help me ?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should not be storing data as comma separated strings, so your idea of splitting it into another table is correct.
    This process is called "database normalization". Read up on it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    storing multiple comma-delimited values in a single column is a disaster -- don't do it

    instead, use the additional table, but do not create a separate auto_increment column for it

    CREATE TABLE hotel_facilities
    ( hid INTEGER NOT NULL
    , fid INTEGER NOT NULL
    , FOREIGN KEY ( hid ) REFERENCES hotels ( hid )
    , FOREIGN KEY ( fid ) REFERENCES hFacilities ( fid )
    , PRIMARY KEY ( hid, fid )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2008
    Posts
    6
    thank you very much blindman , i'm gonna read it
    also thank you r937, i got it now

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Interesting naming convention; hid and fid...
    What happens if you have two tables that start with the same letter?
    E.g. rooms and reservations?
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2008
    Posts
    6
    Quote Originally Posted by georgev
    Interesting naming convention; hid and fid...
    What happens if you have two tables that start with the same letter?
    E.g. rooms and reservations?
    hehe, maybe i can use 2nd letters like roID and reID do you have another idea?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    room_id
    reservation_id

    They become pretty self explanatory this way too, don't you think?
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2008
    Posts
    6
    i must think because you are guru HA not me
    Last edited by izmarit; 12-11-08 at 11:52.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by izmarit
    i must think because you are guru not me

    you didn't get the HA part
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2008
    Posts
    6
    i fixed now

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by r937
    storing multiple comma-delimited values in a single column is a disaster -- don't do it

    instead, use the additional table, but do not create a separate auto_increment column for it

    CREATE TABLE hotel_facilities
    ( hid INTEGER NOT NULL
    , fid INTEGER NOT NULL
    , FOREIGN KEY ( hid ) REFERENCES hotels ( hid )
    , FOREIGN KEY ( fid ) REFERENCES hFacilities ( fid )
    , PRIMARY KEY ( hid, fid )
    );

    Standard many to many relationship, need a junction table instead of comma delim values.

Posting Permissions

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