creating a child row with two parents? (in one column)

Hi!

How can I make a relation that would let me relate an employee to two departments?

There is a table with emloyees and each one works in a department and there is a foreign key relation in the child (employees) table to the parent (departments) table. So can an employee work in two departments? (it's not impossible to imagine, is it?) And how would I retrieve all the employees of a department?

Or in other words, if I were working on some kind of software for my local supermarket and they need to keep track of all bills they ever gave out. I would make this products table and this bills table. Then I would create a foreign key column in the products table and fill it with bill_ids from the bills table and make retrieving bill items a simple getchildrow[](bill_row). Note that this is only an analogy, I need it done this way. How? (using C# express 2005, and SQL express and DataSets)

Use 3 tables, Employee, Department, and EmployeeDepartment. The table EmplyeeDepartment contains 2 fields only, EmployeeID and Department ID (actually, you might want to give it an IDENTITY column too, but logically it contains just the 2).

|||For more information look at the definition of n-m relationships.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||OK. Thank you. Wouldn't it be a lot easier if the cell that is designed to contain the foreign key could contain an array by default? We would just have a simple query as in one to many and SQL would search through these elements. I still think it's unbelievable that such a thing has not been implemented.

So as a resume,.. there is this dB that keeps track of movies: It has two tables: movies and actors:

movies -> title* | productionYr | director | ... | cast | ...
actors -> firstName* | lastName* | birthDay | ... | picture | ...

So, to relate an actor to the cast column in movies I have to create an intermediate table
MovieActor -> title* | firstName* | lastName* for each actor in every movie?

There has to be an easier way to do this, I think I've set it up right and the solution seems complicated..
Btw, this movies analogy is almost the same as the problem I'm working on..|||

Hi,

an array would be not as good to maintain and query like the n-m relationship. You table design should be something like the following:

Movie table
========
MovieId
MovieName (etc. columns)

Actor Table
=========
ActorId
ActorName (ect. Columns)

MovieToActor Table
================
MovieId
ActorId

Assuming that you want to find out how much movies the actor With a specialId was involved in you would crawl through EVERY Movie in the movie table and chop the array in pieces to find out the right movies. In the above mentioned case you only would have to query the MovieToActor Table to find out the same thing, this is much more easier (!).

If you have any further questions, don′t hesitate to write back.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Ok. I understand, it makes sence to do it this way. Only, the actorToMovie table will tend to get very very very long

If we assume that only a limited number of actors can star in a movie...I would make my movies table like this: (like a flag cell only wiht content)

Movie:
id | Title | year | etc.. | actorId01 | actorId02 | actorId03 ...

and have a "select * from movies where ((actorId01 = someId) or (actor02 = someId) ....)"

Would that waste more processing power than it would save space? (Empty cells don't take up space, do they?) It would probably NOT save processing power as the dB engine would have to search through all records (in the actorToMovie table) anyhow, or would it as it would have less records (rows) to look in? (in above mentioned Movie table)

Thank you..|||Hmmh, no this is really preferable. Do you know how many actors will act in a movie ? WHat about if someone wants to input all of the actors from the battlefields from Star Wars Clone Wars in the movies table. You will be pretty fast run out of columns, not to mention that the queries will get quite big and slow, so this is really really not practicable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||OK. I see that point, but I've said "assume". As this is not a movie application, I can know for sure that no "movie" will have more that 18 (or so) "actors". Would that make my aproach practical?

In general terms, is there a way to figure out the limit value for the quanity of possible "actors" that would make this approach practical. Obiously, if only two actors could appear in a movie, a seperate table might not be the best solution.|||From a design point it really is. Based on my experiences due to extensibiliy, maintainability and ease to use this is the best solution. Right now your are having a limited but varying amount of actors in the table. What about the assumption that there could be more one day ? What about if you extend your table to 18 or more columns and you also use the movie table for storing animal movies (with no actors until you give them names, just kidding :-) ). Would that be 18 columns with no values ? As i said before that should be the most practical way to do this. But perhaps there might be another one giving his two cents about that from the groups.
I will ping another expert for this to convince you :-)
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Use the Force Luke :)

Thank you.|||

>if only two actors could appear in a movie, a seperate table might not be the best solution.

Yes it would. The waknesses of poor design may not cause a problem in some situations, but good design generally works well in all situations.

|||

Reading through this thread I see a couple of things:

" if the cell that is designed to contain the foreign key could contain an array by default? "

Not at all. An array would require different handling than a seperate table. What is the difficulty in having another table? The fact is, the SQL language was meant to work with sets of scalar values. Period. Not arrays, not vectors (which the 2-18 column approach you also mention is.) Joins are not nearly as colstly as would be interrogating an array in every row to determine the number of values that are related.

"Movie:
id | Title | year | etc.. | actorId01 | actorId02 | actorId03 ...

and have a "select * from movies where ((actorId01 = someId) or (actor02 = someId) ....)"
"

But it is so much more natural to say:

select *
from movies
join movieActors
on movies.movieId = movieActors.movieId
where movieActors.actorId in (someId, someOtherId,...)

The major deficiency in your query is that looking for multiple values like this (someId, someOtherId,... ) would take tons of values in the where clause. Sure, for two max values this is a problem, but what about 18? Or 1800. The normalized design is not limited in solution to a given number.

And when you want to join to the actor table,

select *
from movies
join movieActors
on movies.movieId = movieActors.movieId
join actors
on movieActors.actorId = movieActors.actorId

is very optimizable.

select *
from movies
join actors
on actors.actorId = movies.actorId1
or actors.actorId = movies.actorId2
or actors.actorId = movies.actorId3

will likely be horrible to optimize.

"(Empty cells don't take up space, do they?) It would probably NOT save processing power "

The space saved would likely be with your solution, without the intermediate table. But, this is more a matter of how SQL is suited to be used rather than a matter of space. The space difference will be very slight, if anything. The processing however would be great.

Bottom line is, building this table:

id | Title | year | etc.. | actorId01 | actorId02 | actorId03

and using SQL on it is like buying a box of screws and a hammer. If you want to do all of the processing the hard way manually, then you can hammer the screws in. But designing tables that conform to what the creators of SQL had in mind is like purchasing a power screwdriver to use screws with. It just plain fits.