May 6, 2010 at 5:47 am
Hello,
I've a system, that have two types of users (Companies and individuals).all types have a shared set of properties but they differ in another. What is the best design merge all in one table that allows null for unmatched properties, or separate them in two tables related to a basic table with a one to one relationship.
Thanks.
May 6, 2010 at 6:08 am
Assuming you're talking about an OLTP application, I would always tend towards separating the data rather than just NULLing columns that are not relevant to the type.
It just makes life easier if for example you want to make a column compulsory for one of the distinct types, you can just make the column NOT NULL rather than using a more complex check constraint etc.
There's no absolute best practice though, it often depends on the individual circumstances. Having said that, companies and individuals sound like very different things to me.
May 6, 2010 at 6:20 am
I agree.
A company is not an individual and an individual is not a company. Further, you wouldn't want a one-to-one relationship between the two. I work for a company with more than 4000 people. You don't want to list the company 4000+ times. You list the company once and then relate it to the individuals.
The trick is, do you want the individuals to only relate to one company? If so, you can put that foreign key constraint inside the individual table. But if not, you'll need to create a relationship table, usually referred to as an interim table. This will allow you to define multiple relationships between multiple companies and multiple individuals. You'll probably want to add a relationship type table so that you can store values there like 'employee' or 'customer' and use those to define the relationship.
It can get even more complicated than that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2010 at 8:46 am
You said that both companies and individuals are basically 'users', which I assume means that they both login to the application with a profile and perform similar tasks. There could be a supertype called something like [appuser] with company and person as sub-types.
appuser
appuser_company
appuser_person
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 6, 2010 at 8:55 am
I'd separate them. I've dealt with this before and when someone rewrite the rules slightly, or you merge companies, move people, etc. it's painful.
I'd learn towards separate tables for the entity. Then use a table that handles security if these are users and relate that to a table as mentioned above by Eric
May 6, 2010 at 9:23 am
Put them all into one XML column. ;^)
The probability of survival is inversely proportional to the angle of arrival.
May 6, 2010 at 10:02 am
If there are well defined logical relationships between types of users, then you definately need to enforce this with foreign key constraints, or else referential integrity and performance issues will get out of hand. For example, let's assume you have an application that is used by healthcare providers, patients, and care givers.
create table appuser
(
constraint pk_appuser primary key (appuser_id),
appuser_id int not null
);
create table appuser_provider
(
constraint pk_appuser_provider primary key (provider_id),
provider_id int not null
constraint [fk_appuser_provider__provider_id]
foreign key references appuser (appuser_id)
);
-- a patient is assigned to one provider:
create table appuser_patient
(
constraint pk_appuser_patient primary key (patient_id),
patient_id int not null
constraint [fk_appuser_patient__patient_id]
foreign key references appuser (appuser_id),
provider_id int not null
constraint [fk_appuser_patient__provider_id]
foreign key references appuser_provider (provider_id)
);
-- a caregiver is assigned to one or more providers and one or more patients:
create table appuser_caregiver
(
constraint pk_appuser_caregiver primary key (caregiver_id,provider_id,patient_id),
caregiver_id int not null
constraint [fk_appuser_caregiver__caregiver_id]
foreign key references appuser (appuser_id),
provider_id int not null
constraint [fk_appuser_caregiver__provider_id]
foreign key references appuser_provider (provider_id),
patient_id int not null
constraint [fk_appuser_caregiver__patient_id]
foreign key references appuser_patient (patient_id)
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 6, 2010 at 1:23 pm
sturner (5/6/2010)
Put them all into one XML column. ;^)
You made my day! 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2010 at 1:26 pm
mashtal_et_1982 (5/6/2010)
I've a system, that have two types of users (Companies and individuals).all types have a shared set of properties but they differ in another. What is the best design merge all in one table that allows null for unmatched properties, or separate them in two tables related to a basic table with a one to one relationship.
Not totally clear to me.
Other than having "human" and "corporate" users... are you storing specific data for them or happens that different kind of users are interested in different sets of the same underlying data?
If as some poster assumed there are two sets of data - one for "humans" and one for "corporate" then I will agree - at least - two tables are needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply