mysql - One to Many Table linked to a One to Many Table -

i'm new mysql(3hours under belt),just finished reading php & mysql dummies 4th edition , i'm create database contains information shops practice.

the database i'm trying create contains information list of stores.each store contain basic information store,the industry(e.g clothes, food) store operating in, inventory.

while have table containing store's name,description(which can short write and/or url store's website), , store id(which serves primary key)

create table merchant(  merchantid      serial, industry        enum("retail","dining","entertainment"), name            varchar(1000) not null, description     varchar(1000), primary key(merchantid) ) 

each store have multiple categories selling, , each categories have multiple items.would right in saying looking @ one(store) many(categories) table linked one(category) many(items) table? reason being although first table(store categories) has 1 many r/s, second table(category item) has 1 many, , not many many r/s looking @ singular category contains multiple items in it.

table categories:

create table categories(  categoryid      serial references merchantid, category        varchar(50) not null, primary key(categoryid)  ) 

table items:

create table items(  itemid      serial references categoryid, item        varchar(50) not null, primary key(itemid)  ) 

is code above correct? or have enter primary keys 1 level above. e.g:

create table item(  merchantid      serial, categoryid      serial, itemid          serial, item            varchar(50), primary key(merchantid,categoryid,itemid) ) 

furthermore, there difference between having varchar(50) , varchar(1000) seeing mysql automatically rid of unused space?also, there way further optimise database performance etc.?

when create relationship between tables (one-to-many) should have primary key (pk) foreign key (fk), pk has 1 side , fk has many side. in case got wrong concept of using fk serial data type. example have merchant table (your one-side) structure this:

create table merchant(  merchantid      serial,  industry        enum("retail","dining","entertainment"),  name            varchar(1000) not null,  description     varchar(1000),  primary key(merchantid)  ) 

and related table categories (the many-side)

create table categories( categoryid      serial references merchantid, category        varchar(50) not null, primary key(categoryid) 

first let's tackle serial data type. serial data type in mysql bigint not null auto_increment data type pk. so, in category table merchantid auto_increment ok. now, in category table primary key categoryid serial auto_increment references merchantid. so, example if store 'a' has 2 categories first value pk in merchant table 1. in category table since auto_increment means have values 1 , 2. in 1 value match 1 value of merchant 2 value not match because there none (except add store). in other words values of pk (merchantid) in merchant table not coincide values of fk categoryid in categories table.

in other words don't use serial bigint in related field, namely, merchantid. so, should now.

 create table merchant(  merchantid      serial,  industry        enum("retail","dining","entertainment"),  name            varchar(1000) not null,  description     varchar(1000),  primary key(merchantid)  )  create table categories( merchantid      bigint, category        varchar(50) not null, primary key(merchantid, category) ) 

now, since want create relationship between category (one-side) items (many-side) have retain categoryid in table changes categories table to:

 create table categories(  categoryid      serial,  merchantid      bigint,  category        varchar(50) not null,  primary key(categoryid),  foreign key(merchantid) references merchant(merchantid)  ) 

now in items table one-to-many, is, category have many items items table change this:

create table items( itemid      serial, categoryid  bigint, item        varchar(50) not null, primary key(itemid), foreign key(categoryid) references categories(categoryid) ) 

this 2nd point. in case items being many-side has fk categoryid categories pk categoryid.

and agree dan brauck don't use enum data type because difficult extend or not flexible. so, merchant table should have structure now.

create table merchant(  merchantid      serial,  industry        varchar(50) not null,  name            varchar(1000) not null,  description     varchar(1000),  primary key(merchantid)  ) 

i hope way understand normalization concept important in database design. links below:

http://www.tutorialspoint.com/sql/first-normal-form.htm https://en.wikipedia.org/wiki/database_normalization