SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    FK's with triple value PK. help

    Hi,

    I am not ready to yet put this into the db so I would like clarification from anyone here, on whether I have it correct.

    two tables

    Code:
    CREATE TABLE IF NOT EXISTS business_type 
    ( business_type varchar(25) NOT NULL
    , business_sub_type varchar(32) NOT NULL
    , business_category varchar(32) NOT NULL
    , PRIMARY KEY (business_type,business_sub_type,business_category)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
    
    CREATE TABLE IF NOT EXISTS business_details 
    ( business_id int NOT NULL auto_increment
    , business_type varchar (25) NOT NULL
    , business_sub_type varchar(32) NOT NULL
    , business_category varchar(32) NOT NULL
    , tic varchar (64) NOT NULL
    , PRIMARY KEY (business_id)
    , CONSTRAINT businessdetails_businesstype_fk 
        FOREIGN KEY (business_type,business_sub_type,business_category) 
          REFERENCES business_type (business_type,business_sub_type,business_category)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm going to say that this structure is not okay

    type and subtype seem to be related to each other, and category is then related to the pair of them?

    i just have a bad feeling, is all

    could you give some sample rows of data from the first table, using non-trivial values (although they can of course be obfuscated if you don't want to reveal sensitive business data)

    by "non-trivial" i mean don't say type A subtype A1, type B subtype B1 -- that's just too confusing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeh it could be wrong but I haven't been able to think of a simpler way.

    I think the three categories need to be in a table of their own because when enabling businesses to input their data to the db, they will be doing so with select boxes using a pre-determined list, and not by entering whatever they like, to a textbox. As usual, however, I am prepared to stand corrected

    thought about it some more: I think I have been continuing to plan the db on the basis of how the html tables will look instead of simple data storage, I guess.
    Edit:


    Maybe I should just use an auto_increment_id in the table for business_types. I had that in the first incarnation of the db but I thought this way above might have been better

    sample data:

    Accommodation, hotel , 4star
    Accommmodation, hotel, 3star
    Accommodation, guesthouse, 3star
    Dining, Restaurants, Indian
    Dining, Bistros, Italian

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your sample data clearly indicates a 3-tiered hierarchy

    you should have one table for this hierarchy, see Categories and Subcategories

    it's fine to use an auto_increment for the categories table

    then each business would link only to the appropriate level (top, second, or third) of the hierarchy with a cat_id foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah I think I see.

    So I could have a table for the categories, like this

    Code:
    create table if not exists categories
    ( type varchar (18) NOT NULL
    , sub_type varchar (20) NOT NULL 
    , cat varchar (24) NOT NULL
    , PRIMARY KEY (type)
    , foreign key category_fk (category) 
          references categories (type)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    and use varchar data as shown in my sample data above. ie, no numerics?

    Instead, would it be as efficient (I understand it better and also have the insert coding done [if that is relevant]), if I used the table as shown previously and reference it from business_details with the auto_increment FK? It sounds like you said yes to that already but you might have been speaking about the type of table structure your link showed.

    btw, thanks for the link ~ another of yours I guess from the favicon.

    bazz

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    CREATE TABLE categories
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , descr VARCHAR(99)
    , parent INTEGER NULL REFERENCES categories (id)
    );
    INSERT INTO categories VALUES
    ( 1, 'Accommodation', NULL )
    ,( 2, 'Hotel', 1 )
    ,( 3, 'Guesthouse', 1 )
    ,( 4, '4star', 2 )
    ,( 5, '3star', 2 )
    ,( 6, '4star', 3 )
    ,( 7, '3star', 3 )
    ,( 8, 'Dining', NULL )
    ,( 9, 'Restaurants', 8 )
    ,( 10, 'Indian', 9 )
    ,( 11, 'Bistros', 8 )
    ,( 12, ' Italian', 11 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy, that reads easily. but in the actual db, would you store the data taht way or would it be more like this? (with the parents at the top)

    ( 1, 'Accommodation', NULL )
    ,( 2, 'Dining', NULL )
    ,( 3, 'Hotel', 1 )
    ,( 4, 'Guesthouse', 1 )
    ,( 5, '4star', 3 )
    ,( 6, '3star', 3 )
    ,( 7, '4star', 4 )
    ,( 8, '3star', 4 )
    ,( 9, 'Restaurants', 2 )
    ,( 10, 'Indian', 9 )
    ,( 11, 'Bistros', 2 )
    ,( 12, ' Italian', 11 )

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    let me ask you: what difference does it make?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll reflect on that rudy. likely there is no relevant different.

    bazz


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
  •