Help: Design the database for a school

emdproduction@hotmail.com · *nix forums beginner Joined: 17 Mar 2006 Posts: 4

I was asked to help with design a school admission database. I am not
familiar with designing the database at all, hope this group can help
me. The database purpose is to store all student's infomation. So I
am going to use student_key at primary key for student table, and use
student_key as foreign key for all the other tables. Could all the
expert here tell me is it OK design?

student(
student_key prmary key
lname,
fname,
mname,
birth_date,
sex,
ethic origin,
high school code, etc)

address(
student_key foreign key,
address1, address2,etc)

test_scores
(
student_key foreign key,
test_type,score, etc)

emergency_contact(
student_key foreign key,
fname,
lname,
phone, etc)

application_detail
(
student_key foreign key,
date_received,
etc)

previous_college
(
student_key foreign key,
college_name,
transcript_date_received, etc)

attendance(
student_key foreign key,
matriculation code,
number of records for this term,etc)

Thanks very much for your help.

Brian Peasland · *nix forums Guru Joined: 04 Apr 2006 Posts: 301

Only you can really tell if your database design is ok or not. Database
design starts with your business rules and determining which entities
you wish to model. Once you know the entities, discover the attributes
you need to capture for those entities. Then discover how those entities
relate to each other. All of the entities, attributes, and relationships
are defined by your business rules. While I may have a business rule
that states the Zip Code of a student must follow the ZIP+4 convention,
you may not have that same business rule. Therefore, our attributes may
look different. Apparently, you have a business rule that you must
capture the ethnic origin of the student (as it is an attribute for the
STUDENT entity). I may have a business rule that states we will not
store ethnic origin's so that it cannot be used to determine any
business case based on this information. For legal reasons, my business
rule might be that we never store ethnic origin for anyone.

Looking at the design that you have presented, I would ask why the
address information is stored in a different table? Is the address just
another attribute of the student? If so, then these should be attributes
of the STUDENT entity and reside in the STUDENT table. But it may be
your business rule that a student can have multiple addresses (school
address and permanent address) and that multiple students can have the
same address (roommates, for example). In this case, the address becomes
an entity in its own right. See how the business rules define all of
this? Similarly for the emergency contact information.

Start with the business rules, determine your entities, attributes, and
relationships. From there, the initial database design starts to flow.
No one can answer these questions but yourself. Even two business that
perform the same function for its customers can have two different sets
of business rules.

Cheers,Brian

--
===================================================================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

emdproduction@hotmail.com · *nix forums beginner Joined: 17 Mar 2006 Posts: 4

Brian,

Thanks for your reply. I think what I asking is like this:
Will it be OK for a table has a huge number of columns as long as long
as I follow the 1st, 2nd, and 3rd normalization? Or should I make this
huge-column table into several tables with less columns?

Thanks

Brian Peasland wrote:

Brian Peasland · *nix forums Guru Joined: 04 Apr 2006 Posts: 301

emdproduction@hotmail.com wrote:

Google