I was wondering what was the best design I could make for reusability, long-term maintenance and scaling for my database design. I am currently the only programmer at my job and they need a good database for a future project. All the application will use is this database for local user login, for customer (which can be also a company) tracking etc.

The database management system I will use is MySQL.

Basically what I want to implement is a database that will keep track of all the users we have to eventually configure some ATA automatically. Furthermore, I need this database to contain customers as well as companies (which are customers also). I need this database to contain users, which are employees. I want to fusion both "databases", but I am not sure what is the proper way to do it.

Business rules

  • A Party can have multiple Email
  • A Party can have multiple Phone
  • An Employee is a Party
  • A Customer is a Party
  • A Company has a Party as a Contact
  • A Company can have multiple Address
  • A Customer can have multiple Address
  • A Customer can have multiple ATA (Not shown in my diagram)
  • A Company can have multiple ATA
  • There are exactly-three PhoneTypes: 'Home', 'Office' and 'Cellphone'
  • A Phone can be of exactly one PhoneType

So a Customer is essentially a Party but it has Address(es) while an Employee is a Party who has restriction. I don't know how to fit Company in there. My current design has Company attached to Customer with a one-to-many relation(one customer, multiple companies).

In short I want to create a database that contain Companies, Customers and Employees login information (no pay check involved here). How can I optimize my (application program) UML schema to fit all those criteria?

Additional information

Here is how my new design works, I am still not sure of the Company integration in my schema:

Schema 2

I thought that Company and Customer are quite the same so I made them both Customer. Perhaps adding a column for ­IS_COMPANY could do the trick?

Responses to comments

What can be phone type? How many phone types can exist? Since there can be multiple emails, phones and addresses, why not keep them in one table called contact. Here's a small version of your problem. - KumarHash

Phone type has three possibilities: "Home", "office" or "cellphone". I made it that way so I can load my dropdown values from the relevant table. Party is the contact table you mentionned; I coukd have named it like that, but I chose "party" after I read another question on SO.

Your Answer

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

Browse other questions tagged or ask your own question.