During my database I acquired a listing of companies with posts CompanyId and Title. Further on there's customers and individuals customers can also add items. Tables: user and user_items There's no product table determining items, they're unique per user therefore "user_product"
Whenever a user adds an item he types a title of the company. In the event that company title is available in the organization table I wish to create a link with the organization table rather than saving just the title around the user_product. To date so great.. I simply store CompanyId within the user_product table.
The issue is once the user makes its way into a title that does not is available in the organization table. Rather than saving the title as varchar, I wish to produce a new record inside a table known as user_company. The table got posts: UserCompanyId (PK), UserId, Title. When the combination Title and UserId already is available i'll absolutely not produce a new row, just mention of the this id.
What must i do in order to conserve a good database design here.. Must i add this record in addition to a new column in user_product known as UserCompanyId. To ensure that either CompanyId or UserCompanyId is definitely set when adding a brand new row. It feels as though this may be completed in an easy method. Anybody got any ideas?
I possibly could obviously have only one table "company" and also have a column UserId that is null when it is a worldwide company added through the system, or even the UserId is really set whenever a user has added a business title that did not been around globally. This does not feel great either...
Really, I believe you nailed it inside your last paragraph. A business is either based on a person or is not, therefore the userId is sensible like a nullable column. This could also give you a distinctive key on the organization title, which enables you to employ the database to enforce the truth that a business title can not be copied.
Your organization table is available to define companies--which user (or whether a person) produced the organization is simply details about a business.