0

Given the model below:

CustomerType1(id, telephone, address)
CustomerType2(id, telephone, name)

OrderType1(id, timestamp, customerType1.id, comments, enum1)
OrderType2(id, timestamp, customerType2.id, comments)
OrderType3(id, timestamp, name)

How would I model the following?

OrderList(id, OrderType.id, ..)
OrderItem(OrderList.id, MenuItem.id)

A. Would I need 3 different types of OrderLists in order to adapt to the orderTypes? 

OrderList1(id, OrderType1.id, ..)
OrderItem1(OrderList1.id, MenuItem.id)

OrderList2(id, OrderType2.id, ..)
OrderItem2(OrderList2.id, MenuItem.id)

OrderList3(id, OrderType3.id, ..)
OrderItem3(OrderList3.id, MenuItem.id)

Or

B. Would 3 definitions of a relationship between orderLists and OrderTypes be better? 

OrderList_Type1(orderList.id, orderType1.id)
OrderList_Type2(orderList.id, orderType2.id)
OrderList_Type3(orderList.id, orderType3.id)

This seems like a really inefficient way to store data and I just feel like i've modelled this really incorrectly (although it still makes sense, it might not be good for scaling/efficiency?). Is there a better way to model this?

Note: the given model can be changed but it would still have to contain the same information.

spxcxlxxs
  • 57
  • 8
  • Your question is incomplete. You don't explain "MenuItem" and you do not describe your requirements. It seems to me that you shouldn't model different customer types and order types, but just `Customer` and `Order`. An order normally consists of order lines, so you would normally need an `OrderLine` type/table. With your "OrderList" you probably mean `ItemCatalog`. – Gerd Wagner Aug 23 '14 at 10:57

1 Answers1

0

1. Your UML model is ok

From the point of view of UML class diagram your model and the OrderList, OrderItem extensions you want to add are clear and unambiguous and I don't see any modeling question there.

To avoid excessive copy/pastes I have only added 2 parent classes named as ...Base. It is common OOP modelling technique

Drawn as UML class diagram your model looks like this:

enter image description here

2. For the physical implementation I would choose B

As for the implementation "model" of this model from the two choices you gave ((A) many copy/pastes, (B) somehow normalize and minimize the schema) I would go the (B) path drawn below.

It is how one of our company's software systems models class inheritance in the relational language and it works and it works quite well.

In our system most of the necessary glue code is automatically generated. Main thing is the automatically generated OrderType2View which automatically joins corresponding field from the parent table OrderTypeBase and automatically translates all DML operations e.g. the insert as DML operations in both OrderType2 and OrderTypeBase automatically adding correct OrderTypeClassId fields to all records in the parent table. So that it is easily distinguishable which child table actually contains the specific part of the record.

Thanks to the generator we can easily extend the model with other parent classes (the inheritance hierarchy and the number of joined tables can be of any depth) and still enable some older code to treat them as their general parents - without caring about the details.

enter image description here

I don't know if there are better ways, given the (A) or (B) I would choose (B) because it is a design that works (I have seen it :)

xmojmr
  • 8,073
  • 5
  • 31
  • 54
  • Thanks @xmojmr ! - This was an answer I was looking for. Im just a little rusty on understanding the diagram. Could you link me a resource? Or a quick explanation? For example: the table OrderType1 has 4 columns (id, customerType, comments, enum) what does the bottom section represent? Thanks a lot! – spxcxlxxs Aug 24 '14 at 13:32
  • @sqset the upper diagram is just standard UML class diagram (see http://www.uml-diagrams.org/class-diagrams-overview.html). The lower diagram is also class diagram with notation used by Enterprise Architect (the tool I used to draw it) to represent "physical data model" (see http://www.sparxsystems.com/resources/uml_datamodel.html). <> and <> in the the bottom section were drawn automatically by the tool and they give names to and represent "Primary Key" and "Foreign Key" (see http://en.wikipedia.org/wiki/Foreign_key) – xmojmr Aug 25 '14 at 03:39
  • @sqset at first I wanted to draw the lower diagram as the more usual Entity Relationship Diagram (see http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model) but I did not have a handy EER drawing tool right at hand (like [Toad Data Modeler](http://en.wikipedia.org/wiki/Toad_Data_Modeler)) so I gave you the blue PK FK thing.. – xmojmr Aug 25 '14 at 03:43