table structure/keys question

[IBazz]

Hi,

The old brain has fogged over so I need to ask for some help.

I have two tables - as shown below. I need to make a third table for the many to many relastionship. should I use the coposite key from page_categories in the third table or should I create a surrogate one.

I have tried so far to use data-driven keys (rather than surrogate), for ease of reading/following the data in child tables. However, I reckon such large data in a key is either inefficient or, it'll confuse me later into thinking this must be the parent, because it holds so much data/so many columns.

so whachadidoo?

CREATE TABLE page_categories 
( business_id int NOT NULL
, control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL default ''
, page_category varchar(99) collate utf8_unicode_ci NOT NULL
, category_number int(11) NOT NULL
, PRIMARY KEY  (business_id,control_panel_section,page_category)
, KEY business_id (business_id)
, KEY pageCategory_privilegeAreas_fk (control_panel_section)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 
CREATE TABLE website_nav_menus
( business_id int(11) NOT NULL
, nav_menu_name varchar(99) collate utf8_unicode_ci NOT NULL
, PRIMARY KEY  (business_id,nav_menu_name)
, KEY nevMenu_navMenuStock_fk (nav_menu_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
create table pageCategories_navMenus
(  business_id int NOT NULL
, control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL default ''
, page_category varchar(99) collate utf8_unicode_ci NOT NULL
, nav_menu_name varchar(99) collate utf8_unicode_ci NOT NULL
, live_dev varchar(4) not null default 'dev'
, on_off varchar(4) not null default 'off'
, primary key( business_id, control_panel_section, page_category, nav_menu_name, live_dev, on_off )  
, unique key (business_id, nav_menu_name, live_dev)
)

Having now written out the third table, I am wondering if I am over-engineering this because there is a lot of repetitive data - even though it's referencing from another table.

bazz

[r937]

please explain live_dev and on_off, and why you felt it necessary to include them in the primary key

for simplicity's sake, let's say your two tables are called X and Y, and they have PKs bolded as follows --

X ( bid, cps, pc, cn )

Y ( bid, nmn )

now you want your relationship table like this --

XY ( bid, cps, pc, nmn, live_dev, on_off )

if you include live_dev and on_off in the PK, this would allow the same { bid, cps, pc, nmn } multiple times in the XY table, whereas i would've thunk you'd only want one

[IBazz]

Thanks rudy,

the live_dev column can hold one of two values - 'live' or 'dev'
and the on_off column can hold 'on' and 'off'

live_dev is to differentiate between a live environment and a development environment such as a development website to prepare for replacing the live one.

on_off is for storing whether in either environment, that record is active or inactive.

'Think' nav menu buttons for a live site and a dev site. I may want to disable a nav menu link when the dev site is being built but the live site may still require it to be 'on'.

However, the answer you gave seems to suggest I was fumbling with it well.

bazz

[r937]

yeah, that's what i thought those columns were for

so if this is your table: XY ( bid, cps, pc, nmn, live_dev, on_off )

then you can have only one row per PK --

( 1, 2, 3, 4, 'dev', 'on' )

and 1,2,3,4 cannot also exist for 'prod'

but if this is your table: XY ( bid, cps, pc, nmn, live_dev, on_off )

then you can have

( 1, 2, 3, 4, 'dev', 'on' )
( 1, 2, 3, 4, 'dev', 'off' )
( 1, 2, 3, 4, 'prod', 'on' )
( 1, 2, 3, 4, 'prod', 'off' )

all 4 rows can exist simultaneously

obviously you want to define the PK to meet your actual requirements


okay, and now the coup de grace -- how would any of this differ if you use surrogate keys instead of the composite natural keys?

see if you can do what i've done in illustrating the possible rows, but using surrogates...

[IBazz]

I like your hint at using prod instead of live, especially when live can have two pronounciations. bit of a silly work, if you ask me

So my cols are now

( bid, cps, pc, nmn, prod_dev, on_off )

and primary key is

( bid, cps, pc, nmn, live_dev )

I think. I won't want two records where one is for 'on' and the other for 'off'. they will be an either/or value but there will be a record each for 'prod' and 'dev'. There will always be a dev site and always a live site (certainly there will always be the need to support that eventuality), and, in respect of on_off, the values will either be 'on' or 'off' - not two records where one is on and the other off.

thanks,

bazz

[IBazz]

well I think I could use a surrogate pk but when querying, I would need to join the tables. I reckon that querying will be done much more often than record creation and so, it should be simpler not to require a join when querying.

anyhoo, my surrogate suggestion would be

( bid, category_id, nmn, live_dev, on_off )

| 1 | 3 | primary | dev | on |

Is trying to reduce the need for a join, the main reason for using natural (foreign), keys?

bazz

[oddz]

PRIMARY KEY( business_id, control_panel_section, page_category, nav_menu_name, live_dev, on_off )

One day that is going to be a wonderful foreign key and URL reference to deal with - just saying.

[r937]

we're well past that, oddz