Database design

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
sisleysusie
Forum Newbie
Posts: 16
Joined: Wed Jan 14, 2004 9:44 pm

Database design

Post by sisleysusie »

Hi everyone,
This semester i am taking database design as one of my subject and i am using mysql. I am required to design a database for student registration which consists of individual and group registration which i will put in one database called registration. The atribute for both registration are almost the same, but there are some atribute that are different also. I was thinking of creating two tables, one for individual_registration and another one is for group_registration. Will i be having any problem if these two tables are created since most of the atributes in these two tables are almost the same? Will it cause data redundancy? Primary key for individual registration is id, and as for group registration it will be groupid. I am just wondering whether my database design is ok or not. I will need to do search script for it as well. So i must make sure that both tables is ok. Any advice from experts? Your help is greatly appreciated. Thank you :D
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

I'm not an expert, However, as long as they are two seperate tables I imagine you should be in good shape. Without any such problems. However, when you create a search. You will have to have an option box to select what table to search.

Also, are the fields in each table the same? If so have you ever thought about just making it one table. Then adding a field called "type" and in the type set it to like "group" or "non-group" and do it that way?
sisleysusie
Forum Newbie
Posts: 16
Joined: Wed Jan 14, 2004 9:44 pm

Post by sisleysusie »

Thanks pinehead18. I did think of creating one table, but as what i have mentioned there are some attributes that are different in both tables. Since then, i am quite lost because i am not sure how to start with my database design. Should i create one table or two different tables? Any opinion? :( Thank you in advance
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

Well if you have diffrent attributes then i would defintly go with two tables. Its easy to work with that way to.

I could be wrong but, that is what i would do
sisleysusie
Forum Newbie
Posts: 16
Joined: Wed Jan 14, 2004 9:44 pm

Post by sisleysusie »

Thank you pinehead18. Let us wait for more replies and we see what others will say. I am agree with you but i am just afraid of data redundancy. I wonder whether there will be any data redundancy if i create two tables or not. Hmm.. still wondering..
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

It's a shame you have to use MySQL for this, i'd have thought a Database Design course/class could have used a 'better' backend, PostgreSQL for example.
Not much use to you if you are stuck with mysql but if you were using pgsql then you could have made use of it's schema's and inheritence features which are great for tables that have common fields but can vary slightly. In mysql you pretty much are stuck with using 2 tables, but you can have 1 'main' table and put the differences in the 2nd table and use foreign key constraints to maintain referential integrity (presuming you are using a mysql version that supports it).
sisleysusie
Forum Newbie
Posts: 16
Joined: Wed Jan 14, 2004 9:44 pm

Post by sisleysusie »

mysql is an open source and is quite popular, i guess. That is the reason why i would like to use mysql. But i am still wondering can i create two tables as what me and pinehead18 have discussed in the earlier replies? Is it ok if i do it like that? Any opinion?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Yeah, on the face of it it does seem like 2 tables is the way to go, but without knowing what the fields are and what the differences are between the 2 type of registrations is a little tricky to go further and start hinting at how the tables should be designed/setup.

(PostgreSQL is open source too and has a much more flexible license than mysql, just by the way ;))
sisleysusie
Forum Newbie
Posts: 16
Joined: Wed Jan 14, 2004 9:44 pm

Post by sisleysusie »

Thanks Mark. However, i am wondering whether can i use the same name for attributes in both tables? Or should i use different name? For example, firstname for individual and group_fname for group table. Or can i use same names for the attributes in both tables for example fname in both individual registration and group. Any advice? Thank you in advance
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Re: Database design

Post by xisle »

sisleysusie wrote:I am just wondering whether my database design is ok or not.
Show us the table and field breakdown
and we can give you more feedback.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

My 2 cents:

How many tables you create really depends on the projected queries you'll be using against these tables. If you split the data between multiple tables only to find out you need to use multiple left joins often, then the overhead involved probably isn't worth it. Plus, with harddrive space being as available as it is these days, a little redundancy ain't gonna hurt much.

Having said all that, on paper it seems that you should split your data into three tables.

Table 1: Registration
- This table contains all fields that are common between the two different registration types.
- It also contains a varchar field called RegistrationType that is either "Individual" or "Group".
- Primary Key for this table is ID and is auto_increment.

Table 2: IndividualRegistration
- This table contains all the fields pertinent to only the individual registrations
- Primary Key for this table is ID (the same ID as in Registration) and is NOT auto_increment.
- ID is a Foreign Key to the Registration table.

Table 3: GroupRegistration
- This table contains all the fields pertinent to only the group registrations
- Primary Key for this table is ID (the same ID as in Registration) and is NOT auto_increment.
- ID is a Foreign Key to the Registration table.

This method reduces redundancy to a minimum. But, if you need to do queries that use ALL THREE tables or even TWO tables, there will be overhead involved.

If you are looking for a method with minimal overhead create one table. You'll have LOTS of NULL values, but if that's fine with you, then so be it.

Creating two tables would be the wrong approach. Two tables suggests redundancy of information AND overhead.
Post Reply