ERD for a web development company

database-designdatabase-diagramserdschema

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

Customer
Service ———– (like: web development, web design, web consultation, …)
Order-item ——- (like: web development, web design, web consultation, …)
Order
Invoice
Project
Project-phase
Task ————— (like: web development task3, web design task7)
Developer
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:

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

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

Any help with this, would be really appreciated.

Update 1

Refined version of the diagram

Update 2

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

Customer ———- A hungry person
Service ————- Making a burger, Home delivery, Mobile delivery, …
Order-item ——— A burger, …
Order —————- A home-delivered burger
Invoice ————– The printed invoice
Project ————– Serving the customer for a complete year (like a contract)
Project-phase —– Serving the customer for a single day
Task —————– Preparing the kitchen, process the meat, …
Developer ———- The cookie
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.

Best Answer

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.

Related Solutions

Importing order information

I think the best way to approach this would be to have tables like so:

table Customer
    ID
    Name
    ...

table CustomerFields
    CustomerID
    FieldID
    FieldName
    ... (could have things like type, required/optional, size, etc.)

table CustomerOrders
    CustomerID
    OrderID
    OrderNumber
    DeliveryDate
    ... (all your generics and mappable fields)

table CustomerOrderFields
    CustomerID
    OrderID
    FieldID
    FieldValue

By having the table CustomerOrders, you satisfy the ability to map common customer order data to common fields (simplifying reporting at the expense of making the import a little more painful since the fields must be mapped), and the CustomerOrderFields gives you the ability to have the custom fields per customer necessary for the un-mappable data.

The custom fields are still reportable, but not as easily as your generics as they'll come to you in multiple rows (instead of multiple columns). There are some ways around all that depending on your report creator (e.g., pivoting the results).

The only other option would be to do something like this (which, personally, I would avoid):

table CustomerFields
    CustomerID
    Field1Name
    Field2Name
    Field3Name
    ...
    Field99Name

Table CustomerOrders
    (all your generics)
    Field1Value
    Field2Value
    ...
    Field99Value

This has the advantage that all your data is in one row, but also has the disadvantage that it isn't immediately apparent from the row what each value means. The first method can always be joined to the field list to give a good definition of the data in each custom field. In addition, what happens if some company requires 100 custom fields? In the above example, you'd be making changes to your data structures and code whereas in the first example, you'd never have the issue -- customers could have infinite custom fields.

I've seen it done both ways, and both ways work. Both ways have their downsides and upsides. The first is far more scalable, but harder to get in to columns (instead of rows). Everything's a trade-off.

Hope that helps some!

Mysql – Tick boxes and Dropdown

A few points that stand out:

In the table items_options, in your output you have the names like 'Small', 'Medium', 'Large'. If these generic option names are going to be used for different items, as you indicate here:

As you can see items come with same options name but the prices can be different. You notice Balti:Prawns come with extras but Vindaloo:Prawns don't have one.

Then I would change to have an options table, and modify items_options to use the new option_id:

CREATE TABLE IF NOT EXISTS `options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `items_options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `option_id` varchar(50) NOT NULL,
 `price` decimal(6,2) NOT NULL,
 PRIMARY KEY (`id`)
)

This way, if you ever change the name of the option, you only have to do so in one row.

Create a new table items_extras to handle this case:

Sometime I want to apply extras groups to an item which mean it will apply to all related options.

CREATE TABLE IF NOT EXISTS `items_extras` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `extra_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)

The table groups_extras_options is misleading, because it does not utilize your groups_extras.id column. I would rename the table to items_options_extras, so that it would handle this business decision:

Sometime, I want to apply extras groups to specific options from an item.

CREATE TABLE IF NOT EXISTS `items_options_extras` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_option_id` int(11) NOT NULL,
 `extra_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)

Now, you should be able to get a list of all extras that belong to all options for a selected item, as well as all extras for the specific option that was selected.

You can further group them as you want by utilizing the groups_extras table.

Using foreign keys would be a good idea, since you are using InnoDB. The idea is to enforce the referential integrity between these tables. As an example, if you add a foreign key on item_option.item_id that references item.id, you let the database handle situations that you cannot have a particular item_option if the item_id does not exist in the item table. You can read more information on how MySQL handles foreign keys here.

Disclaimer: This was done quickly, so there might be other things to change. These are the points that stood out the most, though. Also, I changed your id columns to be singular because that is my practice.