Greetings everyone. I'm more or less a brand new MySQL user with a bit of experience with databases. I just recently suggested that the company I'm working for should implement a database to track all their customer information. Well they thought it was a great idea and that I should go ahead...so here I am.

Now I've done the initial design in workbench and attached the diagram and I was really hoping for some advice on how it's looking from some more experienced users.

Essentially the company does a lot of dealing with military bases all over the world (especially Canada and the US).

Just to list some of the issues that I thought were more odd and unique so that people can hopefully understand why I did things certain ways...

1) I considered each military base (could be army, navy, marines, etc. or an independent training facility, police headquarters, RCMP base, etc.) a separate company which can have multiple contacts within in...each contact could be ordering for just their squad, unit, etc.

2) The company wants to keep track of current contacts, but, invoices (or quotes or RMAs) sent out in the past should maintain the specific contacts/addresses they were ordered by/shipped to.

3) An invoice should contain a list of all parts included on it as well as the quatity and the price it was sold to in that instance.

4) An invoice can have one address for the person doing the purchasing and one for where it's going

5) They want the ability to add random notes about a certain contact/company


And a couple of my concerns...

1) I have no idea what I'm doing when it comes to identifying/non-identifying relationships....

2) Not sure if a customer can place an order with out a specific person being responsible... (ie. relating tblInvoice to tblCustomer instead of/as well as tblContact)


Anyway, sorry if this is a lot but I really appreciate any help anyone can offer up.

your model looks fine

you need not worry about identifying/non-identifying (unless this is a homework assignment)

i dodn't understand the difference between a solid line and a dotted line

i really dislike your naming convention for tables and foreign keys, but hey, whatever floats your boat

what's an RMA?

The dotted line represent non-identifying relationships and the solid lines represent (you guessed it) identifying ones.

I'm not sure where my naming convention came from...I used Access a bit years ago so maybe it's from there??

An RMA is a returned merchandise authorization...similar to an invoice in form but without prices, it is always coming to us and requires a reason for return on each part involved.


I've also recently made a slight change and added a recursive one-to-one relationship on tblInvoice. tblInvoice is used for quotes as well as invoices and this is to add the ability to link an accepted quote to its corresponding invoice.