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.
- 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?
Here is how my new design works, I am still not sure of the Company integration in my schema:
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.