Creating an SQL Schema (postgresql)

Question

I'm having problems creating a schema for a PostgreSQL project.

It's for a social networking site, if there is a profile, and each profile comes in three varieties: generic, education, and employment profiles, therefore each profile requires different attributes… how do we do this all in the one table?

create type ProfileTypeValue as enum
   ('generic', 'education', 'employment');

create Profiles (
  id    integer
  type  ProfileTypeValue
  ....?
  primary key (id)
);

because for instance if it's an education profile, then we need to have institution name etc, or if it's an employment profile, then we need to have an employer name attribute, etc.

Is it best to just have 3 different tables, 1 for each profile Type, dont know if thats possible… but I feel like I need to have an if statement saying if it's profile, include these attributes, or if its a profile, include these attributes, etc.

Aleksi Yrttiaho · Accepted Answer · 2011-03-27 06:50:14Z

Here's a couple of options

All in the same table
Common profile attributes in one table, profile type specific in their own tables with foreign key references to the common profile table
Inheritance
Key-Value store

All in the same table

In this option all the fields are always present whatever type the profile is. This is too easy to do the first time around as you only have to list all the columns. However, this is really bad design that will make your life harder in the long run, because the maintainability and extendability is poor. You should read up on database normal forms etc. Don't do this.

Master profile table and profile type dependent details on their own tables

In this option you will create a table for all profiles. This will include all the common attributes. This table will make sure the identifiers are all in the same namespace and each profile has a unique id. For each profile type you'll create a new table that has a foreign key reference to the master profile table. You can then select all employment profiles using an inner join on the employment profile table and the master profile table. This design allows you to create constraints for each profile type. Furthermore, this design lets you have profiles that are both employment and education profiles. You should probably do this.

Inheritance

Postges provides a facility for table inheritance. You can use this by creating a base table for all profile types and then creating child tables for each profile type. Each profile type then inherits all the attributes defined in the parent table. With inheritance you can select all profiles using the parent table and all employment profiles using the employment profile table. If generic profiles use only common attributes, they can be stored to the parent table.

The main disadvantage of inheritance in postgres is that parent table and the child tables do not share the same namespace. You cannot create a unique constraint that spans all the tables. This means that you have to make sure that the identifiers are globally unique some other way e.g. keeping a separate table for the profile identifiers.

You should think if the disadvantages of inheritance matter in you situation. However, this is the sensible way of doing separate tables for all profile types if you are using postgres as you don't have to duplicate the definitions of the common attributes.

Key-value store

You could also create a table for common profile attributes and keep the rest of the attribues in (profile, attribute, value)-tuples. By doing this, you'd discard the benefits of a RDBMS and you'd have to implement all the logic in you program. Don't do this.

+1, very nice answer. Personally I'd go for option 2. Option 3 could be done in Postgres using the hstore data type which is very efficient and can also be indexed.
Typo in the comment: hstore is for option 4 (key-value store): postgresql.org/docs/9.0/static/hstore.html.

Babar · Accepted Answer · 2011-03-27 06:50:51Z

1

PostgreSQL supports table level inheritance. You can make a Profile table as the parent table with common attributes and then separate child tables for education and employment with only attributes specific to those categories

Check out the PostgreSQL documentation here.

answered Mar 27, 2011 at 6:50

Babar

2,8363 gold badges26 silver badges35 bronze badges

Collectives™ on Stack Overflow

Creating an SQL Schema (postgresql)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related