CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
Samson Luvanda
Samson Luvanda

Reputation: 17

Difficulty in database design

I am experiencing difficulty in MySQL database design.

I have the following tables:

school_table
id | school_name
---------------------------
1  | success primary school

stage_table
id | stage_name
---------------------------
1  | nursery
2  | primary
3  | secondary

school_stage_table
id | school_id | stage_id
---------------------------
1  |    1      |     1
2  |    1      |     2

school_stage_table is a linking table. This is because there is many to many relationship between school and stage, that is a single school may have many stages, and the same stage may have many schools.

The problem comes here: Each stage has different attributes, and therefore different attribute values for different schools.

How do I model this scenario in a database? Need your help.

Upvotes: 0

Views: 232

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

One option here would be to create a stage_attribute table containing at least the following four columns:

stage_attribute
id | school_id | stage_id | attribute

Each record in this table would correspond to a single attribute for a single stage, e.g.

1 | 1 | 1 | 'nap time'
2 | 1 | 1 | 'breakfast'
3 | 1 | 3 | 'phys ed'

I suspect that some of the difficulty in your mind was with the possibility of adding attribute columns to the stage_table for each attribute. Of course, this is problematical because each stage could have different numbers or types of attributes, and it won't scale for many attributes. The option I gave above eliminates many of these problems by using an arbitrary number of records to store the stage attribute information.

Upvotes: 1

Whencesoever
Whencesoever

Reputation: 2296

You should use table school_stage_table for this different attribute values for different schools.

If You will use the same attributes schema for multiple schools, then there should be one more table called for example school_stage_options with fields 

school_stage_options_id | stage_id | option1 | option2 ....

and later use school_stage_options_id in school_stage_table instead of using stage_id.

Upvotes: 0

Hardik Patel
Hardik Patel

Reputation: 716

As you previously said that you are having some issues that how to store different attributes of each stage of each schools.

Here you can take one table which will store all the attributes of each stage. You can use following table for storing attributes.

Table :

school_stage_attributes_table

id | school_stage_id | attributes_name  |  attributes_value
------------------------------------------------------------
1  |    1            |     attrib_1     |  value_1 
2  |    1            |     attrib_2     |  value_2

Upvotes: 2

Related Questions