SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
Thread: FK's with triple value PK. help
-
Nov 28, 2008, 15:18 #1
- 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;
-
Nov 28, 2008, 21:47 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Nov 29, 2008, 06:44 #3
- 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
-
Nov 29, 2008, 07:11 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Nov 29, 2008, 07:28 #5
- 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;
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
-
Nov 29, 2008, 07:37 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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 )
-
Nov 29, 2008, 07:44 #7
- 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
-
Nov 29, 2008, 07:47 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
let me ask you: what difference does it make?
-
Nov 29, 2008, 08:28 #9
- 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