Page 1 of 1

Table from relational mapping

Posted: Tue Nov 20, 2012 7:35 am
by Live24x7
Hi :)

Following an example from a book, I am trying to develop a
'software development collaboration tool'


The tool has 3 important interacting components.
1) User
2) Project
3) Features

The relationship mapping is as follows:
One user Many Projects
One Project Many Users
One Project Many features
One feature One project
One feature one user
One user many feature


Accordingly the database has 3 tables
1) User
2) Project
3) Features

However the database also conatins a table called project_user_assignment. I do not understand the reason for having this additional table.

My question
Are there any thumbrules that can be used to create tables from a given set of relational mapping ?
What are the guiding principles for deciding appropriate db tables given a set of relational mapping ?

Re: Table from relational mapping

Posted: Tue Nov 20, 2012 12:21 pm
by requinix
One user Many Projects
One Project Many Users
You cannot store that information correctly without a secondary table. If it was just the one then you can put the user ID in the project, or if it was the other then you can put the project ID in the user, but with both you can't do it. Go on: try to find a way to do it that does not involve storing multiple bits of data in one field (like "1,2,3").
So the second table is where all the user+project pairings go.

Re: Table from relational mapping

Posted: Tue Nov 20, 2012 2:21 pm
by califdon
Live24x7 wrote:My question
Are there any thumbrules that can be used to create tables from a given set of relational mapping ?
What are the guiding principles for deciding appropriate db tables given a set of relational mapping ?
There are more than thumbrules, there is a whole body of knowledge known as "the relational model" that has quite definite rules that absolutely must be followed in order to use SQL to query a relational database. Check out the results of a Google search for that term or the author of the seminal book on the subject, Chris Date, or the guy who did all the math back in 1970, Dr. E. F. Codd.

First, you need to have exactly one table for every "entity" defined for your database. In your case, you have already done the work to identify the 3 entities, Users, Projects, and Features. In addition, for every many-to-many relationship you need one more table that relates the foreign keys from the 2 related tables.

Re: Table from relational mapping

Posted: Wed Nov 21, 2012 1:55 am
by Live24x7
@requinix - now i see the point for the additional table.
@califdon - thanks will explore these book refereneces.