Database design question...

  • Thread starter Thread starter Beringer
  • Start date Start date
B

Beringer

Hello,

This may seem long an drawn out, but that is probably because I am
approaching my design incorrectly.

It is pretty easy to model an object with a database, simply create a table
for it and place all its attributes in the table.

What if you have a more complex object, say it contains a variable number of
other multiple objects?

As an example, a bowl of fruit as an object. Can contain any number of
apples, oranges and bananas depending on what the user placed in it.
Now there can be a seperate table for all the fruits of the world, but how
do you model a particular LIST of fruit as being in the bowl? Different
bowls can have different types and amounts of fruit.

One way I have thought of solving this doesn't seem very robust. That is,
every bowl has a key and then simply search every fruit table for that key.
I don't like this idea because it depends on table names and what if you
add, remove or change a table name? That means a coding change. Also there
are many different kinds of fruit, you would have to search every one to
make sure the bowl was filled correctly.

Thank you in advance,
Eric
 
Hi Eric!

Hmm, this one is purly a mathematic question. And it´s about the volyme of
each bowl AND the vilyme of each type of fruit. And since there is a varity
of size for one kind of fruit it´s, in reality, impossible, to calulate
exatrly how many of each fruit may go in a specifyed volyme.

But if we can simplifye the real world for a while and create a standard for
each type of fruit and pretend that every apple (for instance) have a volyme
of XX cm3 then will this task be possible to do!

Every bowl is a unique object and would go in a seperat table (ex. tblBowl)
having field like [BowlID] = AutoNo, [BowlName], [BowlVolyme] and maby some
more.

Each fruittype (bananas, apples etc.) is an object and would go ina seperat
table (ex. tblFruits) having field´s like [FruitID] = AutoNo, [FruitName],
[Volyme], [Colour] etc.

To fill a bowl with fruit you need a third table (ex. tblBowlContents)
having at least three fields [BowlContentID] = AutoNo, [fkBowlID] and
[fkFruitID] where [fkBowlID] and [fkFruitID] is of long type. The relations
between these three tables would look like this:

tblBowl................................tblBowlContents....................tb
lFruits
BowlID (ONE)---(MANY) fkBowlID
.............................................fkFruitID (MANY)--- (ONE)
FruitID

If you would make this task even more complex you would not use a starndard
volyme for each type of fruit you would use diameter, height and lengt and
shape of each type of fruit and then calculate the volume but also include
the shape in the formula for filling the bowl. Because a bowl can look like
anything and can be a cube or a half ball. It can look like a vase and so on
and even if you have a bowl with a valyme able to be filled with 5 apples it
may not be possible due to the opening of the bowl is to small.

He, he, not as easy as we would like it to be, or!?

Anyway the tablestructure would look like the one abowe and I might have
created a couple of more fields in tblBowl (OpeningArea for instance).

This is my two cents of knowledge!

// Niklas
 
Now there can be a seperate table for all the fruits of the world, but how
do you model a particular LIST of fruit as being in the bowl? Different
bowls can have different types and amounts of fruit.
Click to expand...

This is a pretty good example of the most basic relational database
concept: a one (bowl) to many (fruit) relationship; or (depending on
how you are thinking about it) a many (bowls) to many (fruits)
relationship.

First case: a table of Bowls, with attributes pertaining only to the
bowl itself not its contents; related one to many to a table of
Fruits. The Fruit table would have one record per item of fruit, and
one of its fields would be the BowlID, a foreign key to the Bowls
table.

Second case: A table of Bowls, as above; a table of Fruits, with
records for Gala Apples, Bosc Pears, Kumquats, etc; and a third
"contents" table with fields for the BowlID, the FruitID, and perhaps
a Quantity field; a record with BowlID 31, FruitID pointing to Bosc
Pears, and Quantity 3 would indicate that the bowl contains three bosc
pears (at least until I get too close, if they're ripe!) Other records
in the Contents table would contain pointers to the other types of
fruit.
 
Back
Top