Managing record groupings

Hi group,
I cannot figure out the best way to manage some data I have to put into a
database without doing something il-advised. Imagine this scenario:
Table: Animals
AnimalID AnimalType
1 Cat
2 Dog
3 Ferret
4 Iguana
5 Orangutan
6 Nurse Shark
7 Binturong
8 King Snake
9 Moth
10 Crawfish
11 Pelican
12 Man
13 Porpoise
14 Ermine
15 Seahorse
Fine, so there's a long list of animals, say a few hundred, and what an end
user needs to be able to do is select any number of animals and assign them
to carriers for transportation. The end user may select that one animal
type is alone, or one animal will travel with anywhere from 1 to
count(animalid)-1 animals. So, I can't do anything like:
AnimalID AnimalType TravelsWith01 TravelsWith02 TravelsWith03
That would be silly. But I can't figure out how to make a table that can
manage an unlimited number of AnimalIDs that would indicate that they are
related in some way (will travel together). There won't necessarily be a
fixed number of traveling containers either. Like, I can't do:
TravelContainerID Animal1, Animal2, Animal3
That would also leave me with a bunch of Animal columns that wouldn't make
sense to have exist. The way I'm thinking about it now is:
MatchID AnimalID
1 1
1 6
1 9
2 3
2 11
3 12
4 7
4 15
4 10
4 5
4 13
And so on. So, that would mean that animals 1, 6, and 9 travel together,
and so on. But something about that doesn't seem right either. Can anyone
offer advice for my design please?
Thank you,
Ray at workRay,
I think your end solution is fine. You have basically
created a join table that contains yours "Transit ID"
associated with the animals in that "Transit ID". You
could now add a shipping ID to track how the animals were
sent and still be able to tell what group they were in.
Hope that helps.
Derek
>--Original Message--
>Hi group,
>I cannot figure out the best way to manage some data I
have to put into a
>database without doing something il-advised. Imagine
this scenario:
>
>Table: Animals
>AnimalID AnimalType
>1 Cat
>2 Dog
>3 Ferret
>4 Iguana
>5 Orangutan
>6 Nurse Shark
>7 Binturong
>8 King Snake
>9 Moth
>10 Crawfish
>11 Pelican
>12 Man
>13 Porpoise
>14 Ermine
>15 Seahorse
>Fine, so there's a long list of animals, say a few
hundred, and what an end
>user needs to be able to do is select any number of
animals and assign them
>to carriers for transportation. The end user may select
that one animal
>type is alone, or one animal will travel with anywhere
from 1 to
>count(animalid)-1 animals. So, I can't do anything like:
>AnimalID AnimalType TravelsWith01 TravelsWith02
TravelsWith03
>That would be silly. But I can't figure out how to make
a table that can
>manage an unlimited number of AnimalIDs that would
indicate that they are
>related in some way (will travel together). There won't
necessarily be a
>fixed number of traveling containers either. Like, I
can't do:
>TravelContainerID Animal1, Animal2, Animal3
>That would also leave me with a bunch of Animal columns
that wouldn't make
>sense to have exist. The way I'm thinking about it now
is:
>
>MatchID AnimalID
>1 1
>1 6
>1 9
>2 3
>2 11
>3 12
>4 7
>4 15
>4 10
>4 5
>4 13
>And so on. So, that would mean that animals 1, 6, and 9
travel together,
>and so on. But something about that doesn't seem right
either. Can anyone
>offer advice for my design please?
>Thank you,
>Ray at work
>
>.
>|||Thank you Derek.
Ray at work
"Derek Wilson" <derek_wilson@.rmic.com> wrote in message
news:016f01c34006$3d6dbfd0$a501280a@.phx.gbl...
> Ray,
> I think your end solution is fine. You have basically
> created a join table that contains yours "Transit ID"
> associated with the animals in that "Transit ID". You
> could now add a shipping ID to track how the animals were
> sent and still be able to tell what group they were in.
> Hope that helps.
> Derek
> >--Original Message--
> >Hi group,
> >
> >AnimalID AnimalType TravelsWith01 TravelsWith02
> TravelsWith03
> >
> >That would be silly. But I can't figure out how to make
> a table that can
> >manage an unlimited number of AnimalIDs that would
> indicate that they are
> >related in some way (will travel together). There won't
> necessarily be a
> >fixed number of traveling containers either. Like, I
> can't do:
> >
> >TravelContainerID Animal1, Animal2, Animal3
> >
> >That would also leave me with a bunch of Animal columns
> that wouldn't make
> >sense to have exist. The way I'm thinking about it now
> is:
> >
> >
> >MatchID AnimalID
> >1 1
> >1 6
> >1 9
> >2 3
> >2 11
> >3 12
> >4 7
> >4 15
> >4 10
> >4 5
> >4 13
> >
> >And so on. So, that would mean that animals 1, 6, and 9
> travel together,
> >and so on. But something about that doesn't seem right
> either. Can anyone
> >offer advice for my design please?
> >
> >Thank you,
> >
> >Ray at work
> >
> >
> >.
> >