Database design with composite types in postgresql

Question

How to refer another table built from composite type in my table.

I am trying to setup a database to understand postgresql and its Object oriented features.

The statement is as follows : There are multiple companies which can have board members. Each company can own another company or a person can own that company too.

This is the type of database design I am looking for.

create type companyType(
name: VARCHAR,
boardMembers : personType[],
owns: companyType[]
)

create type personType(
name: VARCHAR,
owns: companyType[]
)

Create table company as companyType
Create table person as personType

I understand that I cannot self reference the companyType so I will probably move this another table.

My question is, when I am trying to insert into say company type, how do i insert list of person table objects as foreign key ?

Would making a column 'id' in each table and giving it type SERIAL work to use it as a foreign key?

Postgres doesn't really have many object-relational features. Most importantly, it does not have any reference types, and even if you would bring your own serial column it is not possible to create a foreign key constraint for the elements of an array type column, or even for the elements of a composite type column. — Bergi
– Bergi, Commented Oct 8, 2019 at 21:39
PostgreSQL creates OID keys for each row in the table ( please correct me if I am wrong ) cant we use these keys to refer it from another table. Eg- an OID key of a person table row say #123 can be used in Company table to add as board member ? — toitnoice
– toitnoice, Commented Oct 9, 2019 at 4:56
No, the WITH OIDS option is effectively deprecated. Sure there are still some system columns that identify individual rows, but they cannot be used as keys. — Bergi
– Bergi, Commented Oct 9, 2019 at 7:54

Laurenz Albe · Accepted Answer · 2019-10-08 21:33:23Z

1

That is not a relational database design, and you won't get happy with it.

Map each object to a table. The table columns are the attributes of the object. Add an artificial primary key (id bigint GENERATED ALWAYS AS IDENTITY). Don't use composite types or arrays.

Relationships are expressed like this:

If the relationship is one-to-many, add a foreign key to the "many' side.
If the relationship is many-to-many, add a "junction table" that has foreign keys to both tables. The primary key is the union of these foreign keys.
Normalize the resulting data model to remove redundancies.
Sprinkle with unique and check constraints as appropriate.

That way your queries will become simple, and you can use your database's features to make your life easier.

answered Oct 8, 2019 at 21:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

toitnoice Over a year ago

Hey, yes I know this is not an relational design, I am not trying to design it as per the conventional relational DB methods, but trying to understand how postgreSQL works, which is an OOP extension of SQL. We can define composite datatypes and create its lists and for each entry in a table the database itself creates an OID which can be used to refer the row from other tables ( just like a foreign key ). I am having problems with inserting data into such a design and need help with it. If you have worked with postgreSQL it would helpful if you help me.

Laurenz Albe Over a year ago

Which data abstraction layer are you using? I am surprised to learn that there is one that can work with such a data model. Typically, an object oriented model is mapped to a relational model in the database. I believe that the difficulties that led you to ask this question are a symptom of a badly chosen data model.

Collectives™ on Stack Overflow

Database design with composite types in postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related