Is there a best practice equivalent of interfaces in database design?

[PhatoseAlpha]

I'm working on a project that involves handling customer accounts. In working out the database specs though, I've run into a situation that I'm sure someone here as run into before.

We need to handle accounts, some of which are tied to a person, some of which are tied to a company. I don't want to just make them all 1 record which includes a name field and a company field, as that would seem to violate normalization rules. In code, I would just make the field an interface of have both person and company implement it. But in a database, I don't know.

I see two possibilities - one is to make a third table, entities, and reference that from both person and company fields, and in the accounts reference the entity table. I could also have the account have two fields, one indicating whether the id being referenced was a person on a company then the id - but that prevents me from using foreign key restraints for that field.

I figure this situation has come up before thousand of times, and there's probably a best practice for it - one of those two, or perhaps a third option I haven't thought of. My googling skills have failed me though. Any recommendations?

 

[Markbnj]

Actually, in this case I would make them a single table with both a name and a company field, and perhaps a type field to indicate whether it is an individual or corporate account (although that can also be indicated by the presence of null in the company field). To me this is a basic question of normalization. You don't want to duplicate entities if you don't have to, but on the other hand you don't want to create additional indirection where it isn't needed. It sounds like all you're trying to save here is a single name field, which if it is varchar won't take up any space when empty anyway. Additionally, if you try to normalize it further you'll probably complicate business layer code like authentication and account administration.

 

[PhatoseAlpha]

There were additional questions of usage though.

Genuine Companies should be allowed to have multiple names tied to them, and possibly multiple branches, while single customer accounts should not. I figured I'd be able to use the person table to store individual employees of specific companies as well as non-corporate accounts.

If I'm storing company data the same place I store the person data, then I run into inconsistency issues with companies - one employee is of "ABC company" while another is under "A.B.C. Co.". Figured it was a many to one or none relation between people and companies, so companies should be on a separate table.

 

[JustAnAverageGuy]

I would probably have both company and person in the same field 'AccountHolder' and have another field 'EntityType' to differentiate between them.

Have a separate table for company data which references the primary key of the Accounts table.

 

[Hmongkeysauce]

What I would probably do is the following:

Account(PersonId, AccountName)
Person(Id, FirstName, LastName, CompanyId)
Company(Id, Name)

This will allow you to assign multiple person to a company as well as multiple accounts to a company. If Person.CompanyId is null, you know that the account is not a company account. This doesn't allow you to directly assign accounts to a company, however.

EDIT
I think I totally missed the part about you implementing an interface in your code. This design will most definitely break that code. Sorry.

 

[KentState]

I would go with an Account and Account Type table. At some layer you will have to implement business rules around how the account types affect multiple entries. I would bake that into your stored procedures. I'm no longer convinced that the schema is 100% responsible for enforcing complex integrity. NoSQL has pretty much shown us that there are many ways to do this.

 

[PhatoseAlpha]

All interesting responses. I have to say though, I'm surprised there isn't a single best answer for this though. RDBMS's have been around for decades, and this kind of polymorphism can't be that rare.

 

[Markbnj]

All interesting responses. I have to say though, I'm surprised there isn't a single best answer for this though. RDBMS's have been around for decades, and this kind of polymorphism can't be that rare.

Well, relational databases aren't designed for representing classification hierarchies. They're designed for storing and relating sets. This was one of the big arguments in the push for object DBs back in the early nineties, but they never really caught on because relational is good enough.

 

[BrightCandle]

There are three classic answers for how to map inheritance into a database:
1) All in one table with a field that distinguishes the type.
2) Have a base table that includes common properties and a type field, then separate tables for each of the different types, the tables should share the same primary key so they can be joined.
3) Create a table per type and then you search in all of them to find the right one.

Which is appropriate depends on the volume of the queries, types of queries and traded off against the complexity of the code written to dehydrate the objects.

 

[EagleKeeper]

A company have be worked with multiple names
a person may also represent multiple companies.

Best to have a third table for the linkage between a person and company.
One may have to force the user to select the company that the person is representing - possibly by a last referenced flag within the table - present that option combination as the default

 

[beginner99]

I would say 3 tables:

create table AccountHolder(accountHolderID int primary key,...)

create table Person(accountHolderID int primary key references AccountHolder(accountHolderID), ...)

create table Company(accountHolderID int primary key references AccountHolder(accountHolderID), ...)


Company can now reference additional tables like one for the different company names. An Account has a reference to AccountHolder and so forth.

 

[brandonb]

I do the following:

account (identity = id_account)
map_account_person (identity = id_map_account_person, indexes on = id_account and id_person)
person (identity = id_person)
map_person_phone (identity = id_map_person_phone, indexes on = id_person and id_phone)
phone (identity = id_phone)

That allows for a many-to-many relationship between all things there. What if you want multiple people tied to the account? Well, you can... What if you want multiple phones tied to the person... Well, you can...