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