Dear somebody,

My name is Brian and I'm in the process of modeling a database for an on-line deli application. To give you a little background I've identified four entities in this real-world model. They are:

Customer,
delivery,
order,
selection.

In the creation of my logical schema I've been following a method from the authors 'elmasri and navathe' in their publication 'fundamentals of database systems'. In a chapter titled 'enhanced entity-relationship and object modeling' they talk about superclasses and subclasses. In my application an 'order' can have multiple 'selections'. ‘selection’ is a superclass and I’ve generalized 'selection' into the following subclasses:

chips,
desserts,
beverage,
meals,
salads,
soups,
baked goods
sandwich,

‘Meals’ becomes another superclass that falls under ‘selection’ and has the following subclasses:

box lunch,
hot dinners
salad / sandwich
soup / sandwich
meal deals

In the process of mapping my EER model to specific relations I've come up with the following tuples:

Selection (selection_id (PK), description, price, qty)
Sandwich (selection_id (CK), Sandwich_ID (CK), bread_id, meat_id, size)
Salad (selection_id (C(composite)K), salad_id (CK), dressing)

The remainder of the sub types look like this...


Chip (selection_id (CK), chip_id)
Dessert (selection_id (CK), dessert_id)
Beverage (selection_id (CK), beverage_id)
.
.
.

Note For the purpose of doing business (specifically, 'ordering' these items) I don't need to specify any specific attributes for the subclass's (chip, dessert, beverage...) with the exception of ‘sandwich’ and ‘salad’.
that have (bread_id, meat_id, size and dressing). My question is "Can you have a subclass with no specific attributes other than those that are defined in the superclass?"


In the event that it helps I will put some data to these entities.

Chip (potato, barbecue, regular, pretzels, baked lays, sunchips, fritos...)

Beverage (coke, diet coke, sprite, fresca, bottled water, minute maid, Fruitopia...)

Dessert (brownies, cookies (raisin oatmeal, white macadamia nut, choc chip), lemon bars)

Baked goods (bread (wheat, white, vegetarian), muffins (blueberry, apple cinnamon...), croutons)

Soup (vegetable garden, cheese broccoli, wild turkey rice...)

Salad (chef, turkey salad...)

meals (box lunch, hot dinner, soup/sandwich, salad/sandwich, meal deal)

box lunch (sandwich, beverage, chip, dessert)

hot dinner (turkey dinner, hot turkey sandwich with potatoes and gravy, barbecued turkey sandwich…)

soup/sandwich (soup, sandwich)

salad/sandwich (salad, sandwich)

meal deal (barbecued turkey sandwich combo w/baked beans for $5.50, half sandwich with soup/salad for $5.30…)




PS. To make it simple should I make ‘selections’ one big table and not worry about the redundancy?

Thank you all very much for any suggestions you can give me
Brian.

Well, that's one tall order. (ahem...I couldn't resist)

What's happening here is you are taking what is also called a generalization hierarchy and stretching it to the max.

I know of no law carved in stone saying that a supertype, such as order, requires its subtype, such as soup, to have additional attributes.

Order(OrderID, OrderType, Attributes...)

Soup(OrderID, SoupID(FK))

Would be valid.

Dear Certus,

Thank you for the reply. In another post

http://groups.google.com/groups?dq=&...69e6d4436ef99c

it was said that EER concepts don't manifest themseleves very often in the real world, though they do occur. Given that it was sugggested that I make things a bit simpler and go off the premise that:

CUSTOMER orders FOOD
FOOD makes up ORDER
Order gets DELIVERY.

Those would be my relations and as such my starting point.

But Food presents a problem When you start tossing such complex objects like "box lunch" which contains food items into an entity FOOD.

There is additional complexity with entities like "sandwich" which contains attributes like "meat, bread and size" in addition to all the other the common food attributes like (description, price and qty).

For better or worse this is why I started generalizing on "food". Please advise if you have the time I really could use the help. thx Brian