FK's with triple value PK. help

[IBazz]

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

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

[r937]

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

[IBazz]

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

[r937]

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

[IBazz]

Ah I think I see.

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

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

[r937]

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 )

[IBazz]

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

[r937]

let me ask you: what difference does it make?

[IBazz]

I'll reflect on that rudy. likely there is no relevant different.

bazz