Database relation problem

Question

In my database I got a list of companies with columns CompanyId and Name. Further on there is users and those users can add products. Tables: user and user_products There is no product table defining products, they are unique per user therefore "user_product"

When a user adds a product he types a name of a company. If that company name exists in the company table I want to make a connection to the company table instead of saving only the name on the user_product. So far so good.. I just store CompanyId in the user_product table.

The problem is when the user enters a name that doesn't exists in the company table. Instead of saving the name as varchar, I want to create a new record in a table called user_company. The table got columns: UserCompanyId (PK), UserId, Name. If the combination Name and UserId already exists i will of course not create a new row, just reference to this id.

What should I do to maintain a good database design here.. Should i add this record and also a new column in user_product called UserCompanyId. So that either CompanyId or UserCompanyId is always set when adding a new row. It feels like this could be done in a better way. Anyone got any ideas?

I could of course only have one table "company" and have a column UserId which is null when it's a global company added by the system, or the UserId is actually set when a user has added a company name that didn't existed globally. This doesn't feel good either...

Answer 1

Actually, I think you nailed it in your last paragraph. A company is either defined by a user or isn't, so the userId makes sense as a nullable column. This would also allow you to have a unique key on the company name, which allows you to use the database to enforce the fact that a company name can't be duplicated.

Your company table exists to define companies--which user (or whether a user) created the company is just information ABOUT a company.