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
Database design
Moderator: General Moderators
-
sisleysusie
- Forum Newbie
- Posts: 16
- Joined: Wed Jan 14, 2004 9:44 pm
Database design
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
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
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
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?
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
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
-
sisleysusie
- Forum Newbie
- Posts: 16
- Joined: Wed Jan 14, 2004 9:44 pm
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).
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
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
)
(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
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
Re: Database design
Show us the table and field breakdownsisleysusie wrote:I am just wondering whether my database design is ok or not.
and we can give you more feedback.
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
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.
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.