Table from relational mapping

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
Live24x7
Forum Contributor
Posts: 194
Joined: Sat Nov 19, 2011 9:32 am

Table from relational mapping

Post 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 ?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Table from relational mapping

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Table from relational mapping

Post 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.
Live24x7
Forum Contributor
Posts: 194
Joined: Sat Nov 19, 2011 9:32 am

Re: Table from relational mapping

Post by Live24x7 »

@requinix - now i see the point for the additional table.
@califdon - thanks will explore these book refereneces.
Post Reply