3

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
1

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.

1
  • 3
    @MichaelGreen this would be better as a comment, don't you think? Or perhaps expand it to a more detailed answer. Jan 15 '16 at 21:08
0

George, it is good to start with data modelling, but it takes some time to learn and understand the modelling practices, for your current requirement check this link Data Modeling Jump Start and Basic Data Model. There are lots of books available, get some materials and build your own data model.

1
  • Thanks rathishDBA.I have read the suggested books but it seem similar with what i have done.
    – George
    Mar 31 '14 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.