Database Design
Moderator: General Moderators
Database Design
I'm trying to grasp the concept of Database Design as it is a very important aspect to the design of a program. In the book I'm reading it uses the example of students, instructors and classes. Students will have certain information pertaining to themselves, such as first name, last name, classes attending, etc. Classes will have certain information such as class name, class number, class description, class Instructor. Instructors will have information such as first name, last name, contact information, etc! You get the point.
After laying that out, the book attempts to walk you through a 3-step process of taking this information and desigining a database to fit it. As far as I can tell, it's using the concept of primary ids/unique ids. I understand the purpose of primary ids, however I don't get the purpose of unique ids. The book, through this 3-step process which is the eventually creation of multiple tables linked together by the ids to provide all the information needed, presents this concept as a method to reduce the overhead in SQL queries (i.e. less work to update records, for instance: since a student takes multiple classes and at sometime the class instructor decides to change the name of the class, instead of having to update multiple records you only need to update one, as all the students in the class are referencing the "class" table using an id of some sort.
If I've confused you thus far, I apologize. As I claimed before, I'm having trouble grasping this subject. Anyone care to clarify?
After laying that out, the book attempts to walk you through a 3-step process of taking this information and desigining a database to fit it. As far as I can tell, it's using the concept of primary ids/unique ids. I understand the purpose of primary ids, however I don't get the purpose of unique ids. The book, through this 3-step process which is the eventually creation of multiple tables linked together by the ids to provide all the information needed, presents this concept as a method to reduce the overhead in SQL queries (i.e. less work to update records, for instance: since a student takes multiple classes and at sometime the class instructor decides to change the name of the class, instead of having to update multiple records you only need to update one, as all the students in the class are referencing the "class" table using an id of some sort.
If I've confused you thus far, I apologize. As I claimed before, I'm having trouble grasping this subject. Anyone care to clarify?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
This is the relational model behind many databases.
Many students can take many classes; a many-to-many relationship. Many instructors can teach many classes; another many-to-many relationship. When dealing with many-to-many relationships, you need a separate table just for it. The reason why is because it should have two columns: table A reference and table B reference.
For more on relational modelling, this may be interesting: http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf
Many students can take many classes; a many-to-many relationship. Many instructors can teach many classes; another many-to-many relationship. When dealing with many-to-many relationships, you need a separate table just for it. The reason why is because it should have two columns: table A reference and table B reference.
For more on relational modelling, this may be interesting: http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf
Pretty much. My confusion is specifically in the use of ids to reference the tables together.
Lets say I have three tables, a students table, a classes table and an instructors table. Each entry in the students table will contain specific information about the student, as well as a reference to the classes table to show what classes the student is enrolled in. The classes table has information specific to the classes and a reference to the instructors table which includes the obvious information on instructors. Thus the three tables are linked together - but with what? Do I use the IDs to link them? For a students entry, I use the IDs of the class entries in the classes table to link, and use the instructor id in the classes table to link that?
Lets say I have three tables, a students table, a classes table and an instructors table. Each entry in the students table will contain specific information about the student, as well as a reference to the classes table to show what classes the student is enrolled in. The classes table has information specific to the classes and a reference to the instructors table which includes the obvious information on instructors. Thus the three tables are linked together - but with what? Do I use the IDs to link them? For a students entry, I use the IDs of the class entries in the classes table to link, and use the instructor id in the classes table to link that?
If you only do the 3 tables you just explained, your students will be limited to 1 class. Since the Student to Class relationship is a many to many (1 student can have many classes, 1 class can have many students), you need an additional table to relate the two together. Something along the lines of StudentClasses, that would, in it's simplicity, just have 2 columns, StudentID and ClassID, and have both set to keys, since you only want one StudentID per ClassID.
The other tables are fine, the instructor ID in the class table just is a one to many, one instructor can theoretically teach many classes, so you only need 2 tables to relate the 2.
The other tables are fine, the instructor ID in the class table just is a one to many, one instructor can theoretically teach many classes, so you only need 2 tables to relate the 2.
feyd: Thanks for the link, I'll definately do some reading on this nature.
TheMoose: Is it a general understanding that when it comes to many-to-many relationship, a "middle-ground" table will need to be implemented so that the two tables can reference each other? Or is it just the solution in this case?
TheMoose: Is it a general understanding that when it comes to many-to-many relationship, a "middle-ground" table will need to be implemented so that the two tables can reference each other? Or is it just the solution in this case?
You should read up on relational databases. Let's work with a more simple layout (for clarity and simplicity) to make it easier to understand
Students table:
and the class table looks like
A student can have more than one class and a class can have more than one student, so this is called a many-to-many relationship. You will need a third table to tie these together. You use the class and student ids to relate them to eachother
Relational table
Every record in this third table describes a relationship between class and student, so if there was a record like this:
This record would mean that student # 5 (you can use this id to find info about him in the students table) is in class #23 (you can also use this id to find info about this class in the classes table)
Did that help or confuse you?
I can be kind of a rambler.
Students table:
Code: Select all
id (student_id in the relational table)
name
address
classes_idCode: Select all
id (class_id in the relational table)
name
timeRelational table
Code: Select all
id
student_id
class_idCode: Select all
id - 1
student_id - 5
class_id - 23Did that help or confuse you?
No, that's quite understandable. I'm trying to use this concept in relation to simplify the concept of updating the database information with the least amount of work as possible. As it stands right now, updating information using the database design we've discussed in the thread thus far is accomlishing that.
I've always thought the best way to understand something is to discuss it anyways.
I've always thought the best way to understand something is to discuss it anyways.
I want to make sure I'm understanding this. After reading the article from the link feyd provided me, I've drawn this ERD (entity relationship diagram) to model what I've been discussing in this article.
Would this be a good rendering of a many-to-many relationship simplified using normalization? As far as I can tell, this would simplify everything for the management of the database, including updating records.
EDIT: Well, it isn't rendering as I hoped it would. It looks fine in NotePad
Code: Select all
students
------------------------------------------------------- student_classID
| studentID (PK) NOT NULL auto increment | |--------------------------------------------------------------|
| fName VARCHAR(25) NOT NULL | \ | student_classID (PK) auto increment NOT NULL |
| lName VARCHAR(25) NOT NULL | --------------- | classID (PK)(FK) NOT NULL |
| studen_classID (PK)(FK) INT NOT NULL | / | studentID (PK)(FK) NOT NULL |
| email VARCHAR(25) NOT NULL | |--------------------------------------------------------------|
----------------------------------------------------- | |
|
classes /|\
----------------------------------------------------
| classID (PK) auto increment NOT NULL |
| className VARCHAR(25) NOT NULL |
| classDescription text NOT NULL |
| classTime time NOT NULL |
| instructorID (PK)(FK) INT NOT NULL |
----------------------------------------------------
\|/
|
|
instructors |
---------------------------------------------------------
| instructorID (PK) NOT NULL auto increment |
| fName VARCHAR (25) NOT NULL |
| lName VARCHAR (25) NOT NULL |
| email VARCHAR(25) NOT NULL |
---------------------------------------------------------EDIT: Well, it isn't rendering as I hoped it would. It looks fine in NotePad
Last edited by Jixxor on Wed Jun 28, 2006 11:25 am, edited 2 times in total.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
You have classId in the students table. You are already creating a join table for students and classes so why are you including the same two fields from the join table in your students table? That is the only thing that stands out to me.
PS This is my 1974th post. I was born in 1974. Spooky coincidence or divine revelation? Oohhh....
PS This is my 1974th post. I was born in 1974. Spooky coincidence or divine revelation? Oohhh....
Congratulations on that strange post cost.Everah wrote:You have classId in the students table. You are already creating a join table for students and classes so why are you including the same two fields from the join table in your students table? That is the only thing that stands out to me.
PS This is my 1974th post. I was born in 1974. Spooky coincidence or divine revelation? Oohhh....
As for the issue, I've corrected it. But I'm wondering the change affects the design at all? Was it the right change?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
The design of the database should be such that the DB allows for the fast serving of data. Indexes are always god to use, non-redundant tables/fields are good, etc. It is more of a normalization/optimization thing, I would say.
As for your tables, you may want to add one to join classes and instructors. Each instructor can teach many classes. Unless you plan on offering only one class per id, then each class can have many instructors, so you need a table to join the two.
As for your tables, you may want to add one to join classes and instructors. Each instructor can teach many classes. Unless you plan on offering only one class per id, then each class can have many instructors, so you need a table to join the two.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
In keeping strict relational tables, yes. There are other (ugly) ways of doing it, but it circumvents built in cascading if setup. You can easily create a code-based "relational" table of sorts, which unfortunately my boss tends to do with things. You have a column in the table that is text (for longer length), and you just put each of the class IDs in that column and separate them with a character. In the code you can then just parse those IDs and go from there. But I HIGHLY stress that you don't ever do this unless in extreme cases. It's very poor design.Jixxor wrote:TheMoose: Is it a general understanding that when it comes to many-to-many relationship, a "middle-ground" table will need to be implemented so that the two tables can reference each other? Or is it just the solution in this case?