I'm currently working on a project with Mysql and PHP in order to provide basic time entry, invoicing and payroll support for a small company. I'm not very experienced with databse design so i'm strugling with finding the best solution.
So i'm going to define what I think is important about this system. Our small company has customers. These customers can either be a person, or a company. When a tech enters a time entry for work, we associate it with a customer. I have come up a few solutions that in my view is not really optimal but here they are.
type[either Business or Personal]
userID[If type is Personal then it looks in this field for the user info]
companyID[If type is business then it looks here for companyID in the company table]
customerID[foreign key to customer table]
Now I really don't like having a type field in the customer table, but I can't see any other way to know which table too look into either the user or company without a boolean type telling me which one it should be
The other option was to have a foreign key in the user table and company table linking to the customer table. but that allows a user and company to have the same customer ID. Of course I can restrict that with php or even sql triggers but I'd rather avoid such thing.
As I said I'm really new to database designs so if anyone has past experience with this kind of situation please any input will be appreciated...