Database structure opinion

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
RandomEngy
Forum Contributor
Posts: 173
Joined: Wed Jun 26, 2002 3:24 pm
Contact:

Database structure opinion

Post by RandomEngy »

Hey, I'm about to make a database for a research group, and it's mainly got to do with the people there. There would be about 26 fields of information from them, like id, username, fname, education, interests, email, and stuff like that. Each entry would have different info for the different fields, so my question is, should I split it up into different tables, or keep it all in one big table for convenience? The group has about 20 people, with around 40 alumni now.

Now I'm leaning towards the lazy answer of just having a honking lot of columns in my table, but is there a better way to do it?
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Hhhmmm,

Difficult question to answer really without seeing all the fields you want, but you need to satisfy normalization rules for any database to operate efficiently as a transaction database.

If everyone piece of information is only dependent on the users ID then having them in the one table is okay. But say you had things like department, section etc where section was related to the department then you would want to seperate this. Why? Imagine you had 5 people that worked in the same department/section, lets say the section decide to change its name, you have to update all of the records that have that section name. Lets say that section moves to a different department, you have to update all records with that section to be related to a different department. Maybe it is not an issue when the record sets are quite small, but it is a lot easier to change the data in 1 record in a related table than all relevant records in the user table and a lot easier to write application functionality to handle it and it is a good habit to get into writing the database structure properly no matter how small the dataset.

That is only a couple of issues, try to look at your data in terms of what is really related to each other bit of data, think about the update/delete/insert scenarios you could come across.

Do not duplicate data in each row (example: if you had in your table person, company, company_address, company_postcode. Then if you had more than 1 person working for that company in your table you woul end up duplicatng address info, bad for upates). Do not add in extra columns (example: If some people have more than 1 contact phone number you might be tempted to put ID, User, Name, Tel1, Tel2. What happens if most people have only 1 phone number, waste of database space, What happens if someone has 3 phone numbers, redesign database? and your application?? better to split the data to different tables. Phones isn't a good example because you generally would have phone_number, mobile, home_number but it was the best way I could think of describing it).

Post up a list of all your fields and I will give it a quick look.

Mike
User avatar
RandomEngy
Forum Contributor
Posts: 173
Joined: Wed Jun 26, 2002 3:24 pm
Contact:

Post by RandomEngy »

Thanks for the reply mike. The fields are:

id, username, password, fname, mname, lname, bday_m, bday_d, address, home_phone, cell_phone, office_phone, fax, email, custom_c_name, custom_c_content, status, education, interests, projects, publications, personal_url, custom_p_name1, custom_p_content1, custom_p_name2, custom_p_content2.

The custom ones are for the user to use, to make their own categories. The status is if they're faculty, graduate, undergraduate, etc. The education, interests, projects, publications, contact data, and custom_p fields (as well as name) get generated into a personal page. The name, username, b_day fields, email are used to make a listing of all current members of the group, which can be ordered by name, username, or birthday.

The concept is that each person is in charge of keeping their own information, and that the administrators only make the account for them. Afterwords, the person can change their password and any of the fields (but id, username, and maybe status).

I'm also planning a different table for alumni, with fields fname, mname, lname, degree, graduation_y, graduation_m, thesis, company, company_url, company_location, and email. I'm going to have a function where an admin can change a current user to alumni, changing the "status" field, and adding an entry to the alumni table, based on what they fill in for the fields. The alumni field won't be very dynamic. Changes will be made by accessing the database directly.

Anyway, that's what I've got in mind.
User avatar
RandomEngy
Forum Contributor
Posts: 173
Joined: Wed Jun 26, 2002 3:24 pm
Contact:

Post by RandomEngy »

Don't bother. My laziness took over and now I have just one big table, and it's working out okay. I figure it won't be too bad to have it this way, since there are only about 20 people to keep track of. =D
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

it shouldnt be really bad if you keep the querys down... unless you get like 300+ (my forums have about 25 columns..)
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

RandomEngy wrote:I figure it won't be too bad to have it this way, since there are only about 20 people to keep track of. =D
Fair enough, don't say I didn't warn you :D :D
Post Reply