CODEWITHSUNDEEP
phpmysqldatabase-design
Kanav
Kanav

Reputation: 2819

Should i create two seperate tables just because one different column

I have two type of users for my website: company and individual. They both have almost same form values. Company has additional company_name and individual has additional gender column.

There are 10 field which are common between them. Now, should i create two different tables or should i create single table for both of them?

Upvotes: 2

Views: 88

Answers (4)

TommCatt
TommCatt

Reputation: 5636

There is a principle someone should put their name to -- it being as sure as Murphy's Law or the Law of Gravity -- which goes like this: any shortcuts taken during the design phase of a relational database will come back and bite you in the rear...generally as soon as the day after it goes live.

Call it the "Rue the Day" principle.

You have one entity, Users, which consists of two sub-entities, Companies and Individuals. Proper normalization calls for Company-specific and Individual-specific attributes be removed to separate tables. But there is only one of each such attribute. This seems like a lot of effort and/or waste for little result.

Not at all.

Are you absolutely sure no additional sub-entity-specific attributes will ever be added at a later date?

Thought not.

There are many benefits to such normalization and many weaknesses prevented that make this well worth the little bit of effort. If, that is, it is done properly.

Consider:

create table Users(
    ID      integer   not null auto increment,
    UType   char( 1 ) not null check( UType in( 'C', 'I' )),
    ...     ..., -- common user data
    constraint PK_Users primary key( ID )
);
create unique index IX_UserType on Users( ID, UType );

The UType field is just a flag to designate the type of user, Company or Individual. Each user must be one or the other.

The ID field is the PK of the table so must, by definition, be unique. So you may consider it a bit odd to include this field with UType in a unique index. If ID by itself is unique, any combination of ID and UType must also be unique.

True. But the index makes the following possible.

create table CompanyUsers(
    ID      integer   not null,
    UType   char( 1 ) not null check( UType = 'C' ),
    Name    varchar( 50 ) not null,
    constraint PK_CompanyUsers primary key ( ID ),
    constraint FK_CompanyUser_User foreign key( ID, UType )
        references Users( ID, UType )
);

create table IndividualUsers(
    ID      integer   not null,
    UType   char( 1 ) not null check( UType = 'I' ),
    Gender  char( 1 ) not null check( Gender in( 'F', 'M' )),
    constraint PK_IndividualUsers primary key ( ID ),
    constraint FK_IndividualUser_User foreign key( ID, UType )
        references Users( ID, UType )
);

Specifically, it makes the FK reference possible. So if user 1001, say, is created as a Company user, the user indicator 'C' is contained in the UType field. Thus a row with ID value 1001 can only be inserted into the CompanyUsers table. That value cannot exist in the IndividualUsers table.

So there cannot be company data defined for individuals and vice versa. Moreover, additional Company-specific and Individual-specific attributes can be added later as needed. Other entities which relate only to company users (a contact person, for example) could have its foreign key referring to the CompanyUsers table, eliminating another source of possible errors.

Probably most examinations of User data will need only the common attributes, so only the one table will be used most of the time. So, at an absolute minimum, I would suggest two views, one to show only the Company users with a join to get the company name and the other to show only the Individual users with a join to get the gender. These would satisfy those parts of your application which must consider only one user type or the other.

Such a design is not only safer (less prone to anomalous data) than one using shortcuts, it is much more robust and all around easier to work with.

Upvotes: 2

Fabian Schmengler
Fabian Schmengler

Reputation: 24576

Depends what you are using them for and what these other ten fields are. It sounds like companies and individuals are just different users, but can be used mostly interchangable in your application. Then it makes sense to keep them together in a user table like

user_id, ..., user_type_id, gender, company_name

where gender and company_name can be NULL, and introduce a user_type table

user_type_id, user_type

that holds the types "company" and "individual".

To give a general answer, if company and individual are entities with completely different logic that just happen do have lots of attributes in common, I would keep them separate and at the same time extract common data to other tables where it makes sense. For example, if most of them are representing the address of the individual or company, you could extract them to an address table.

Upvotes: 0

nomistic
nomistic

Reputation: 2962

Companies and individuals seem to be entirely different entities, so unless you are treating them exactly the same, it's probably better to keep them on separate tables.

However, if you anticipate treating companies as people, and they will always be treated as independent units (with no sub-units), you can simply add two keys to your table, one for gender (which is not a required field), and also something like "company indicator") which is a binary value (yes or no).

The real issue, however, is scalability. If you anticipate that at any time, companies will be treated differently that individuals (say, for instance, there's a contact with a company that may change, or if the company splits up), you really should treat them as separate entities. You could have a person indicator on the company table that links to individual; that way you could store information about individual people within the company.

Upvotes: 2

Renzo
Renzo

Reputation: 27424

It depends, if you have other tables with foreign keys referring only one of the two tables you should leave both, otherwise you can merge them in a single table and add a field type.

Note that in case you have foreign keys referring one or both of the tables, you have a third possibility:

create table entity(k1 primary key..., other common attributes...);
create table individual (k1, gender..., foreign key(k1) references(entity));
create table company (k1, foreign key(k1) references(entity));

Upvotes: 0

Related Questions