Database Design

B

Brad_A

How do I set the relationships to do the following:

Each car belongs to a train
Each train belongs to a trainset
There are multiple trains in a trainset.

I can't figure out how to set it up because of the many to many
relationship. How would you set it up?
 
B

Brendan Reynolds

Unless I missed it, Brad (which is always possible) you haven't told us what
the many-to-many relationship is. What you've described seems to be two
one-to-many relationships - one trainset many trains, one train many cars.

In general terms, though, a many-to-many relationship is modelled by
introducing a third linking or 'junction' (no pun intended) table. For
example, suppose, just for the sake of illustration, that a car could belong
to more than one train at the same time. The schema would look something
like this ...

trains (pk train id)
cars (pk car id)
trainscars (pk train id, car id)

one train, many trainscars
on car, many trainscars

but the primary key on the combination of train id and car id ensures that
the same car can not be part of the *same* train more than once.
 
B

Brad_A

Each car can be on a on one trainset... but it can be on a different train.
Each train is a combination of the cars for the set. Basically, if I have 11
cars total and two are unavailable. It is the same set but a different train
number.

I have it to where it checks the car to trainset, but how do I set it up so
that a car can be on mulitple trains, and that each trainset can only include
trains with the cars listed for that set.

Example:
Train Set A

Train A1, has cars 1,2,3
Train A2, has cars 1,3
 
I

Immanuel Sibero

Brendan,
example, suppose, just for the sake of illustration, that a car could belong
to more than one train at the same time. The schema would look something


Or, maybe a more likely scenario, a car could belong to more than one train
at different times?


Immanuel Sibero
 
B

Brendan Reynolds

I know the example wasn't realistic. I just couldn't stand to use
books/authors *again* and couldn't think of a better one off-hand! :)
 
B

Brendan Reynolds

Sorry, Brad. I'm afraid you lost me there. I can't picture what we're trying
to model here at all.
 
B

Brad_A

I will try and simplify it even more.

Each trainset is made up of cars. Each combination of cars is a train. So,
it is possible that you have 15 cars as the whole trains set, but that one or
two are in repairs constantly. Thus, going with 13 or 14 cars. When the
combination is unique, it receives a new train number.

Imagine being in a classroom. The trainset is all those on the roll call.
A train is all those present in the classroom (Sue may be absent today, Tom
tomorrow). There may be many times everyone is present, but there are many
other combinations to the set. How do I set it up to where a car is listed
related to its set and to each train (or combination). What links do I do to
create the many to many relationship?
 
B

Brendan Reynolds

Do we want to record only the set of possible combinations, or each instance
of each combination? For example, on Monday, Train A includes Cars 1, 2 and
3. On Tuesday, Train A includes Cars 1 and 3. On Wednesday, Train A includes
Cars 1, 2 and 3 again. Is this two records (Train A, 1, 2, 3 and Train A, 1,
2) or is it three records (Train A, 1, 2, 3, Monday, Train A, 1, 2, Tuesday,
Train A, 1, 2, 3, Wednesday) ?
 
J

Jeff Boyce

Brad

If you look back over Brendan's responses in this newsgroup you'll find he
is quite helpful.

Do you want him (or any of us who might respond) to blindly answer without a
clear understanding of what you have and what you want, or would you rather
have a thoughtful response?

Jeff Boyce
<Access MVP>
 
B

Brad_A

Each train is going to be a unique number for the most part because in the
example, there are 128 cars, which means it is likely to be unique every
time.

For now, I would go with the one without the day in the name to simplify the
relationship for one particular car to all the trains it is and may belong to.

Regards,
Brad
 
I

Immanuel Sibero

Quite interesting. Based on my understanding of you requirements, to start
with, how about:


tblTrainSet - TrainSetID (pk), TrainSetName, TrainSetDesc, etc.
tblCars - CarID (pk), TrainSetID (fk), CarName, CarDesc, etc.
tblTrain - TrainID (pk), BegDate, EndDate, TrainName, TrainDesc, etc.

Junction table to set up M-to-M relationship between Cars and Trains:
tblCarTrain - CarID (fk), TrainID (fk), etc.


- By itself, the above design would not preclude a train being formed with
cars from different TrainSet, but this requirement can be imposed in the
user interface. Besides, this requirement may change later on (i.e. no
longer a requirement).

- As Brendan suggested, I think you do need to keep track of specific
instances of a train, since a train is not a static entity (i.e. unlike a
car). The use of BegDate and EndDate in tblTrain serves two purposes: 1. It
accounts for the fact that a train is not a static entity over time. 2. It
allows you to account for a train for a period of time (i.e. not necessary
to have one instance of a train per day).


Immanuel Sibero
 
B

Brad_A

Thanks...

I had it really close to that, but not quite. Just to be sure, what would
you use for each relationship? or at least the junction table. I am using
the relationships feature.

Under the current setup, the relationships I have entered are:
Many Car #'s to one Trainset
One Car to CarTrain
One Train to CarTrain

Shouldn't there be more relationship hookup?

Regards,
Brad
 
I

Immanuel Sibero

Brad,
I had it really close to that, but not quite. Just to be sure, what would
you use for each relationship? or at least the junction table. I am using
the relationships feature.

I'm not sure what you're asking here.
Under the current setup, the relationships I have entered are:
Many Car #'s to one Trainset
One Car to CarTrain
One Train to CarTrain

Shouldn't there be more relationship hookup?

I dont know. Based on the requirements you set forth, no. We use tables and
relationships to model the real world. Generically, it's called relational
model or Entity Relationship model. Whether or not you need more tables
and/or relationships depends on the real world you're trying to model, and
only you know that world.

Based on the description of your post, this is what I'm getting:

A- You have Cars
B- You have TrainSet (although after more explanation from you, this is
more of groups of cars - we could have used CarSet)
C- You have Train
D- One TrainSet can have many Cars (1 to M). This relationship is
implemented by having TrainSetID as a foreign key in tblCar.
E- One car can be in many trains at different times, one train can have
many cars at one time. (M to M). This relationship is implemented in a
junction table tblCarTrain.

Points A, B, C above tells me you have 3 entities, points D, E tells me you
have 2 relationships.


So, should there be more relationships? Well depends, is there anything
else that I dont know about (i.e. I'm not getting from your description of
the problem)?

Immanuel Sibero
 
C

Carol Shu

i have a simulal question too, i am working for a used car dealer rship, and
i'm trying to bild a database, my questions is, like vehicle make by
Chevrolet are cavalier, malibu, lumina, caprice, even a pickup, how could i
create a datebase if i just type in vehicle, and all the vehicle models will
shows up...do you understand me? please help (i did set up a combo list for
vehicle make from, so i don't have type in each time, but for a car model i
don't know how)
 
I

Immanuel Sibero

Carol,
You would have a better chance of more people seeing your question if you
started a fresh, new thread.

Immanuel Sibero
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top