Re: Help: Design the database for a school

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_at_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