Abstract databases design.

Question

So I'm creating a web app with different user types that can come from different countries. Examples of the user types would be company, staff etc. Where a company would have a company_name field and staff would not.

In the users database I'm wondering if it's a good idea to implement a one table per column approach i.e for each user attribute there would be a table with a foreign key which would be the user_id and a value for the attribute value.

eg. users.company_name = id(PK), | user_id(FK) | 'company_name' 1 | 1 | company 1

users.email = id(PK), | user_id(FK) | 'email' 1 | 1 | user@email.com

The same could be applied to an address database where different countries' addresses have different values.

Opinions?

i would say no. please think about the queries you will need to write — Randy, Sep 17 '15 at 22:21
no way. you might as well use a single eav table at this point — Jeff, Sep 17 '15 at 22:52
I would disagree with the eav table because there would be no way of setting the correct data types for differnet fields — user2850858, Sep 17 '15 at 23:02
I attempted to explain general database design. If you like, you can go through this example: http://stackoverflow.com/questions/32190581/normalization-in-database/32517139#32517139 — displayName, Sep 17 '15 at 23:50

Neil McGuigan · Answer 1 · 2015-09-17T22:39:08.813

The term you're looking for is "The Party Model"

You want to use Table Inheritance†, also known as subtype/supertype relationships to model stuff like this.

An Individual is a concretion of an abstract Legal Party. An Organization (e.g. a Company) is also a concretion of an abstract Legal Party.

"Staff" is not a subtype of Legal Party. It's a relationship between a Company and an Individual. A company hasMany staffRelationships with individuals.

I recommend Single Table Inheritance, as it's fast and simple. If you really don't like nulls, then go for Class Table Inheritance.

create table parties (
  party_id int primary key,
  type smallint not null references party_types(party_type_id), --elided,
  individual_name text null,
  company_name text null,

  /* use check constraints for type vs individual/company values */
);

I'd go with PostgreSQL over MySQL (or MariaDB) if you're going to use Single Table Inheritance, as the latter do not support check constraints.

You can make user belongTo a party, or make party haveOne user.

† Which is different than PostgreSQL's Inheritance feature.

score 0 · Answer 2 · answered Sep 17 '15 at 22:24

0

I'd create a single users table with company_name and email columns.

For addresses table, I'd start with something simple like this: id, address_line_1, address_line_2, city, state, country, zip.

answered Sep 17 '15 at 22:24

zedfoxus

35,121
5
64
63

The problem with these two table designs is each user doesn't have a `company_name`, likewise different countries have different `address` formats. – user2850858 Sep 17 '15 at 22:32
So what is the problem? Those fields can stay Null – zedfoxus Sep 18 '15 at 00:33

score 0 · Answer 3 · answered Sep 17 '15 at 22:24

With this strategy you'll have to do a lot of joining tables to get a meaningful query result. As a result your performance will suffer and you have very ineffective use of storage.

You should at least combine columns that will typically be combined for a logical entity in your application. So if a 'company' differs from 'staff' in that it has extra columns, you would create a table 'users.company_properties'.

Abstract databases design.

3 Answers3