How to make a better Data model

database-design

I am creating a database system using MySQL for a group in my school (a University). I am new to MySQL and its usage.I have started with the data modelling process, since this process will be the main foundation on which others will be built, It is necessary for me to know if I am heading towards the right direction.

Below is the business rule from the group.

1. They need a database that will store their members information. The
information should include Name and Level (thus their level of
study,we have level 100,200,300 and 400).

2. The database must store information about their executives. The information should  
include the names of president, secretary and
treasurer for every year.

3. A member could hold an executive position for one year,He/She can hold one position 
for more than one year ( so a member can be the
president for more than one year before he/she hands over to another
person), A member could also hold different executive position for
more than one year (so a member who is the secretary in year 1 can
be the president for year 2.

Below is the data model for the information above.

MEMBERS     
--------------                        
MemID: SMALLINT

Name : Varchar(40)

Level: enum(100,200,300,400)


POSITIONS
---------------------- 
PosID: SMALLINT

PosName: Varchar(20)


EXECUTIVES
-----------------    
MemID: SMALLINT-FK1

PosID: SMALLINT-FK2

AcademicYear: Year

Best Answer

As the years tick over will a member's level change? Is it important to have history? For Example Chris was Secretary while at level 100 and became President while in level 300? If so you'll have to separate Level out of Members into a new table and make that the foreign key into Executives.

Related Question