The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Multiple values in a column (db)

I have 2 tables as follows

dept:
deptno (primary key), deptname

employee:
empno (primary key), empname, deptno

Now, an employee can belong to more than one dept. How can I store this employee since there can be only one row for him (being pk). Concatenating deptnos looks like a bad idea.

Bad Idea:
1, 'empname', 3,5

What's the solution?
fahdkamal Send private email
Tuesday, August 05, 2008
 
 
The Situation is further constrained by the fact that every dept has an employee who is the dept head and I want to maintain the 1:1 relationship between the dept and its dept head.

So my bad table would look like

1, 'depthead', '1'
2, 'joe', '1,2,5'
fahdkamal Send private email
Tuesday, August 05, 2008
 
 
One approach is of course to create a deptgroup table as follows,

deptgroup:
deptgrpno, deptno

But I am wary of creating a table with no primary key.
fahdkamal Send private email
Tuesday, August 05, 2008
 
 
You're talking about a "many to many" relationship.  The usual way to deal with this is to have a third table, each row of which links an employee with a department.  Google [many to many database] for examples, eg. http://www.wellho.net/solutions/mysql-many-to-many-table-mapping-pivot-tables.html
Richie Hindle Send private email
Tuesday, August 05, 2008
 
 
Yeah, I'd make a new table deptassignments with a primary key of the two columns

CREATE TABLE deptassignments
{
  empno INT
  deptno INT
  PrimaryKey(empno, deptno)
}
Josh S. Send private email
Tuesday, August 05, 2008
 
 
Josh gave you the right answer.

If you want to learn more about it,  search the web for "many-to-many relationships".
Walter Mitty Send private email
Tuesday, August 05, 2008
 
 
Find someone who has been trained in relational databases?  I'm not trying to be a jerk, but this is a relatively easy problem with a well-known solution.  You could learn this and about twenty other common database solutions by spending a few hours working side-by-side with someone "in-the-know".

And remember ...

"Normalize until it hurts, then denormalize until it works"
Steve Moyer Send private email
Tuesday, August 05, 2008
 
 
==> Multiple values in a column ...

is a very bad idea.
Sgt.Sausage
Tuesday, August 05, 2008
 
 
>> every dept has an employee who is the dept head and I want to maintain the 1:1 relationship between the dept and its dept head.

You would probably implement that by placing a column in the dept. table that gives the employee id for the department head.
David Aldridge Send private email
Tuesday, August 05, 2008
 
 
This sounds like someone's homework problem...
uggh
Tuesday, August 05, 2008
 
 
Go read Database Design for Mere Mortals.  It is *great* and a relatively quick read.  It will teach you the basics and a handful of advanced things.
KC Send private email
Tuesday, August 05, 2008
 
 
Blasphemy :(
Eddy Vluggen Send private email
Tuesday, August 05, 2008
 
 
Ouch. I hope you're a student.
my name is here
Tuesday, August 05, 2008
 
 
You can also use a surrogate primary key in the many-to-many table.
Phillip Flores Send private email
Tuesday, August 05, 2008
 
 
+1 KC.  Helped me a lot when I was first working with databases.
a former big-fiver Send private email
Tuesday, August 05, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz