sql - Mysql Period Column and Junction Table -

our technical adviser in school advised me start creating master tables of entities before creating junction tables. encouraged me use composite primary keys instead of composite unique keys columns.

he told spend of time database before programming gui need make sure table design won't cause problems.

so let's say,

one class has or belongs many schoolyear = true

one schoolyear has many classes = true

makes me think having 3 tables,

(design 1):

table 1: classes

id pk name yearlevelid 

junction table 2: schoolyearclasses

id pk ai classid -- fk references classes(id) schoolyearid -- fk references schoolyear(id) 

table 3: schoolyear

id pk ai schoolyearstart schoolyearend 

is better this

(design 2):

classes

id pk ai name yearlevelid  -- fk references yearlevel(id) schoolyearid -- fk references schoolyear(id) 

schoolyear

id pk ai schoolyearstart schoolyearend 

i need professional advice here because i'm sure guys dealt many systems involving periods. know design affect transaction inserts ill creating joins.

which design better? how can go inserting records if of columns numerical (id)?

i'd appreciate help.

thanks.

for "junction" tables (to implement many-to-many mappings), disagree using auto_increment. have composite pk, plus need pair of ids in reverse order in index. end.

more on schema m:n. gives "whys".

i agree @drew --

1. work on gui while.
2. then have feel selects needed.
3. sketch selects based on both of designs. may become obvious 1 schema better.
4. then finalize schema design.
5. six months later improve schema. (yes, find "final" design not good.)