Creating an SQL Schema (postgresql)

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 p开发者_高级运维rofile 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.

---

Here's a couple of options

1. All in the same table
2. Common profile attributes in one table, profile type specific in their own tables with foreign key references to the common profile table
3. Inheritance
4. 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.

---

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.