1

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...

Improve this question
2
  • Perhaps the fact that there is no table of products is part of your problem. And you should have a definitive list of companies, not schizophrenic tables. You can mark a newly added company as 'provisional' until determined as correct by some administrative process. You can also note the UserID that added the entry. Or the Companies table needs a UserID column too; all companies are user defined, and there can be two unique constraints on the table: (CompanyID, UserID) and (Name, UserID). And you can require that a user only uses company entries for their own UserID. Aconventional, but... Commented Jan 22, 2011 at 21:48
  • As there will be many different langauges and extremely many companies added around the world, there is no way to administer that. Yes, a user can only use his own companies AND of course the ones without UserId that has been globally added. Commented Jan 23, 2011 at 9:02

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yeah maybe this makes sense.. User 2 can't be allowed to use companies added by User 3 though. But that shouldn't be a problem I guess with this setup? This also makes it impossible to have the name as a unique key in that table, I have to use a auto incr. PK id instead.
Sander: Actually, company names in the US aren't necessarily unique. They're only unique within certain geographic limits (usually concerning the issuing of business licenses) or within certain legal limits (having to do with trademark or consumer protection issues, which often overlaps with geographic limits).
I was making an assumption based on part of the question: "The problem is when the user enters a name that doesn't exists in the company table". Upon re-reading, I would make the unique constraint apply to user + company name (assuming a unique company name is the intent).
Ok. I thought so :) Alright, this seems like a good way then. Does anyone else have any input? Or else I'll mark this as answer in a few hours or tomorrow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.