5

I am working on an ERD which represents the relationships between entities in a Web development company behavioral model. There I have entities like:

  1. Customer
  2. Service ----------- (like: web development, web design, web consultation, ...)
  3. Order-item ------- (like: web development, web design, web consultation, ...)
  4. Order
  5. Invoice
  6. Project
  7. Project-phase
  8. Task --------------- (like: web development task3, web design task7)
  9. Developer
  10. Delivery ----------- (like: the developed web app, web consultation session, ...)

I have this diagram so far, though I know it still has major problems, and it is just for a start point:

ERD1

Main concept

  • Customer makes order-items on services, combined to form an order which relates to an invoice.
  • Each project has multiple project-phases with each project-phase related to an order.
  • Each project-phase has multiple tasks, each of which is assigned to a developer.
  • Each project-phase has a delivery, which will be delivered upon payment of the related orders invoice.

Questions

  1. Are has and contains completely the same? If not, how to choose between them for relationships?! what is the measure?!
  2. Are there any rational problems with the Main concept section?
  3. Any helpful ideas or suggestions to improve this model?

Any help with this, would be really appreciated.

Update 1

Refined version of the diagram

ERD2

Update 2

Considering an analogy of a Fast food restaurant would be helpful:

  1. Customer ---------- A hungry person
  2. Service ------------- Making a burger, Home delivery, Mobile delivery, ...
  3. Order-item --------- A burger, ...
  4. Order ---------------- A home-delivered burger
  5. Invoice -------------- The printed invoice
  6. Project -------------- Serving the customer for a complete year (like a contract)
  7. Project-phase ----- Serving the customer for a single day
  8. Task ----------------- Preparing the kitchen, process the meat, ...
  9. Developer ---------- The cookie
  10. Delivery ------------- A burger in wrapped in wax papaer and a drink with the tray

Update 3

(1st comment from Joel Brown)

Project seems to be unrelated to Customer in your diagram. Is this what you intended?

I think Order is and Project should not be related, as I think it is something off-the-counter and customer has nothing to do with it. she/he is only after the delivery and is concerned about time and money, nothing else. Please tell if I am wrong.

Are all Orders part of a Project or can you have orders that aren't in a project at all?

All Orders are part of a project, except those new ones which are waiting in the process queue.

Similarly, you relate order item directly to customer, but also to order. Are orders split between customers?

Please think of Order-Item as these:

  • web design
  • web design consultancy
  • domain name consultancy
  • ...

So, customer choose some of these items, collected as an Order which has an invoice and could be paid. Order-Items are directly related to offered Services. I don't understand what do you mean by Are orders split between customers?.

If not you don't need a ternary relationship between customer, order item and service. Rather, an order item is an intersection between order and service. Order items belong to customers by inheritance from order (or even project?)

This is somehow true: Order items belong to customers by inheritance from order, but I think it is more rational to say it like this:

Order-Items, inherited from Service, belong to Order which itself belongs to Customer.

  • 1
    Use strong terms for the relationship names - preferably ones which the business users use. The name captures the meaning of the relationship, not mearly notes its existance. Weak names, such as "has," often conceal other relationships and allow ambiguous interpretation. – Michael Green Jan 31 '16 at 9:12
  • 2
    I think you need to move from entities and relationships at the conceptual level down one level to logical, where you start to concern yourself with cardinalities. At the conceptual level almost anything can be seen as being related to almost anything else, because they are. However, sooner or later you're going to need to boil this down to the essential relationships. This will happen as part of an initial schema normalization process. At this lower level things will be clearer and you'll understand why I ask, "Are orders split between customers". This is a cardinality question. – Joel Brown Feb 15 '16 at 15:49
3

You're oversimplifying the relations. No two relations in your diagram are the same, since they don't relate the same domains. <ORDER> has <ORDER-ITEM> is not the same as <PROJECT> has <PROJECT-PHASE>. They might both be injective, but that's not sufficient to call them the same.

Try to give your relations more descriptive names. While attributes (aka one-to-one binary relations) tend to be aggregated on a common determinant to create "entity tables", multivalued attributes and more complicated relations like the ternary relation between Customer, Service and Order-item usually become tables on their own. Makes really doesn't describe the relation or resulting table properly.

I suggest you add cardinality indicators to your diagram and give your relations more descriptive names, using the predicate statement of each relation for guidance.

On second thought, those names are probably fine in a conceptual design as long as you read them together with the entity sets they relate. When you get to physical design, 1-to-1 and 1-to-many relations will probably be implemented as attributes of one of the entity sets, so the relation names won't affect the physical design. The many-to-many relations and non-binary relations will become their own tables, for table names at worst you can concatenate the entity names, e.g. CustomerServiceOrderItems.

  • Due to image and screen limitations, I preferred not to mess up the diagram. would you please let me ask what would be more preferred over has, in the <Order>&<Order-Item> or <Project>&<Project-Phase>? – Pmpr Feb 2 '16 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.