Page 1 of 1
looking for DB structure advice
Posted: Thu Jul 26, 2007 6:56 pm
by lafflin
Hello, I am a complete newbie. I have been studying PHP and MySQL for a few weeks. I am trying to design my first DB and would like to just ask anyone who would like to share their thoughts what would be the most logical way to group my fields into tables.
I understand normalization, but when I sat down and started drawing it all out I came to realize that I could create a few really large tables or a whole mess of small ones. Before I post my field list I should tell what it is my DB will do:
The idea for this DB is basically a student/customer management system. it will contain all of a students personal information as well as their parents if they are minors, if not than no records for such information will be necessary. It will also contain credit card and payment information eventually. There will not be any products tables as this DB is designed to support a service. It will need to sort students into classes eventually also.
This DB will never contain more than one thousand records, and from what I think I know I'm aiming for 2NF compliance, 3 would be an overkill. Again, I am a newbie just looking for sugestions on the structure of my DB in terms of what fields I should put in what tables. Thanks in advance for any consideration given to this matter. Here is my list of fields minus time stamps.:
Name_first
Name_Last
Age
child (y/n)
Sex (m/f)
Registration_date
Birthday
Class
School_attending
known_medical_issues
email_address
phone_number
Notes
(parent Info)
Parent_name
Parent_name_2
street_address
City
state
zip_code
Parent_email_address
home_phone
Cell_phone
________________
emergency_contact
Phone
________________
Payment Info
________________
Cadr_holders_name
credit_card_number
Expiration_date
Billing_zip
Card_type
________________________
This is in almost no logical order
Posted: Thu Jul 26, 2007 9:16 pm
by boo
I think that there are a couple of ways to set this up but here is my 5 second stab at it
Table Name: Individual
ID - PK
Name_first
Name_Last
type - Used to Idenify type (ie student, parent, emergency contact)
street_address
City
state
zip_code
Sex (m/f)
Registration_date
Date_of_birth
Class - FK to Class table
School_attending
email_address
phone_number
Cell_phone
work_phone
known_medical_issues
Notes
Individual_Id - FK back to Individual file to link studuents, parents and/or emergency contact
Table Name:Class
Class_id - PK
Class_name
Class_description
Table Name:Payment info
Payment_id - PK
Individual_ID - FK to Individual table
Payment Info
Cadr_holders_name
credit_card_number
Expiration_date
Billing_zip
Card_type
Some of my thoughts on this
There is no reason to store the value of Y/N for Child or Age since that can be determined from the Date of Birth
I wouldnt store CC information but if you are going to store it in it's own table so you can have multiple cards and you can link them to either the parent or student
Posted: Thu Jul 26, 2007 11:19 pm
by lafflin
Your right about the adult (y/n) field. My original intent was to allow some students that might be under eighteen to be considered adults, and the inverse. after more thought though the idea is stupid.
I'm not sure how I'm going to handle the CC info just yet, I haven't even started looking into it yet.
My concern is that if I set the table up the way you sugested than it won't be 2NF compliant. I'm not sure how important 2NF compliance will be with such a small DB though. I'm pretty sure I'll want to create an intermediary table to associate my Student_info table (individuals) to my classes table, but I'm not sure about addresses, should I have a seperate address table? And I guess my other question about 2NF compliance is this: if I have a field that will usually be one of a dozen or less values, but has the potential to be anything like in the case of the school field should I create a sepeate table for it, or does that rule only apply to a field that has a finite set of values?
Am I getting to caught up in normalization?
I hope that anyone trying to read this can follow. Thanks in advance.
Posted: Fri Jul 27, 2007 7:14 am
by boo
I would have to say that they above table Structure is 2NF outside of the address information being duplicated but for a small application this would be fine to do. What you dont want to get into is a case where you are entering too much data so that your users can enter there records. What I mean by this is if you took the city state and zip fields out of the table and created a separate table for them somehow these records would need to be entered into the second table before the individuals records was added.
This can happen a few ways:
1. You enter every possible city, state, zip combo in the area that you will be targeting (this could take a long time and take up a ton of space on your system that you will never need)
2. You have your users enter the information if it is not already there (well this could create bogus combos that are not real and then other users could select this by mistake)
3. You develop a program to search the table when you are entering an individual to see if this combo already exists. If it does then you get the key for the file and store it on the individuals record, if you dont find it then you add it and use the new key on the individuals record (this seems like over kill for a small application).
Now if I was going to have 10s of thousands of records for a small geographical region then I would think about one of the above solutions. Other than that I would just store the information for each record.
But database design is really all up to the developer and personal preference always comes into play with this.
Hope this helps you out
Posted: Fri Jul 27, 2007 4:54 pm
by califdon
While boo has given you some practical suggestions, let me offer you some perhaps academic tips (I used to teach database courses).
Relational database theory is based on some pretty rigorous mathematical theory, as set forth by E. F. Codd and Chris Date in the 1960's. You've obviously read some of this theory, because you discussed normalization. Along with the normal forms are other concepts that address the kind of question you asked. In determining what tables are needed and what columns should be in them, you should always start with identifying the entities you are representing. As you may know, an entity can be a person, an object or item, a place, an event or the like, but it must be readily definable, such as a customer, or a product, or a reservation, etc. If you can't neatly define it, it should be suspect. After identifying the entities, you should decide what properties of each entity you will record. Each entity will become a table and each property will become a column in that table.
Simple, huh? Well, no, it's quite often not so easy to do, and there isn't always a single design that is clearly the best one. But if you start out on a disciplined basis you're a lot more likely to come directly to a good design than if you just start writing down table names and column names and trying to somehow piece them together into a schema.
Lots of folks (even experienced ones, sometimes) tend to skip this analysis or go through it lightly. And they often produce a database that works--or seems to, for awhile. But if there's a chance that you may later need to add new features or if it grows to a really huge size, it often requires a total redesign to accomodate the changes. So I urge everyone who is starting a database project to design it right, the first time. Take the time to analyze what part of the real world you are modeling, identify your entities and properties, establish your relationships, normalize your tables to whatever degree is practical (usually 2NF or 3NF), and only then perhaps relax some of the constraints, once you understand your data model.
Good luck.
Thanks
Posted: Mon Jul 30, 2007 3:19 pm
by lafflin
thanks, that way of thinking about it helps some.