Database Design

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


"Brendan Reynolds" wrote:

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) ?

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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?

"Brendan Reynolds" wrote:

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

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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





"Brendan Reynolds" wrote:

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.

--
Brendan Reynolds (MVP)


"Brad_A" wrote in message
...
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?

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




"Brad_A" wrote in message
...
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


"Brendan Reynolds" wrote:

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) ?

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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?

"Brendan Reynolds" wrote:

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

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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





"Brendan Reynolds" wrote:

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.

--
Brendan Reynolds (MVP)


"Brad_A" wrote in message
...
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?

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 a
Many Car #'s to one Trainset
One Car to CarTrain
One Train to CarTrain

Shouldn't there be more relationship hookup?

Regards,
Brad

"Immanuel Sibero" wrote:



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




"Brad_A" wrote in message
...
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


"Brendan Reynolds" wrote:

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) ?

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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?

"Brendan Reynolds" wrote:

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

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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





"Brendan Reynolds" wrote:

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.

--
Brendan Reynolds (MVP)


"Brad_A" wrote in message
...
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?

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 a
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



"Brad_A" wrote in message
...
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 a
Many Car #'s to one Trainset
One Car to CarTrain
One Train to CarTrain

Shouldn't there be more relationship hookup?

Regards,
Brad

"Immanuel Sibero" wrote:



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




"Brad_A" wrote in message
...
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


"Brendan Reynolds" wrote:

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) ?

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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?

"Brendan Reynolds" wrote:

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

--
Brendan Reynolds (MVP)

"Brad_A" wrote in message
...
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





"Brendan Reynolds" wrote:

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.

--
Brendan Reynolds (MVP)


"Brad_A" wrote in message
...
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?

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)

"Brad_A" wrote:

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?

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

Immanuel Sibero



"Carol Shu" wrote in message
...
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)

"Brad_A" wrote:

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?