By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Design Question - many to many relationship

P: n/a
Hi All,

I have a question about relationships. I'm not so good at designing
databases, just trying to get better.

This is the situation:
I have several objects: Mail, Depot and Action.
Each Mail can have 1 or more Actions, which are specific for the Mail
object
(f.i. create, delete, check).
On the other hand a Depot can have 1 or more Actions, which are
specific
for the Depot object BUT NOT the same as the Mail object (f.i.
create,
delete, validate, update).

For this I've set up a table Mails (PK MailID), MailActions (PK
MailActionID, FK MailID) and Depots (PK DepotID), DepotActions (PK
DepotActionID, FK DepotID).
I've splitted up the Actions because actions for Mail and Depot are
quite
distinct.
So far so good.

Now comes the fuzzy part: I have to create a relationship between Mail
and
Depot and the two possibilities are the following.

One situation is that the Mail is the master: so one or many Depots
can
point towards one Mail.
Mail1 <- Depot1
<- Depot2
or
Mail2 <- Depot3
Second situation is that the Depot is the master: so one or many Mails
can
point towards one Depot
Depot4 <- Mail3
<- Mail4
or
Depot5 <- Mail5
I thought to set up a intermediate table MasterSlaveRelation with a
combined PK: FK MailID, FK Depot ID.
But that doesn't satisfy the need. Because once a Mail or a Depot has
been
set as Master it can only occur once !
So for instance: Depot4 <- Mail1 is not valid ! although its occurence
will
be accepted in the table.

A clue ?
Thanks a lot.

JG

Mar 15 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Greetings,

You have two different situations, so you need two different sets of
tables -- one where Mail1 is Master Depot1 is Detail, and second set
where Depot2 is Master and Mail2 is Detail. It sounds like these tables
would probaly be used as temp tables where depending on the action - you
fill one set with your source data or you fill the other set with your
source data.

You could even simplify this further by not using tables and just create
queries to perform the required actions with your source tables. But
having 2 sets of tables would be the easier route -- for now

Note: code is supposed to make things easier - depending on the size of
the job requirements. If you are dealing with less than 100,000 rows of
data, you can get by with basic stuff, basic queries, basic forms, ...

But if you have a larger system that resembles an enterprise system then
using the basic stuff would result in needing hundreds of tables,
hundreds of queries and hundres of form to accommodate the various
actions/activities - where using code would reduce all these hundreds of
objects to half a dozen.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Mar 15 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.