I'm trying to create a small company database as a personal learning experience in PostgreSQL.

I'm trying to follow Derek Banas tutorial (which is really helpful) but there is something different how my products are processed.

So far I've made these tables as shown in the image Table list

In the components table I have a row 'where_used' where I wanted to put an reference INT to the product where it is used.

But as you can see, some components are used in multiple products. And I can only store one value?

So my question is:
Can someone point me in the right direction on how to get this working?

I tried googling for a solution, But as I'm such a noob I do not know how to get the right keywords for the search...

CodePudding user response：

In data modeling this technic of having multiple sub entities from a super entity is called Inheritage...

As an exemple, you can have a table that is called "Vehicle" and some child tables that are called "Planes", "Boats", "Car".

• The main table (Vehicle) will have shared properties
• The "Plane" table will have some properties specific to planes and so on...

Of course the Primary KEY of vehicle will be shared in an exclusive mode betwwen "Planes", "Boats" and "Cars"... From a child point of view, the tables will have a PRIMARY KEY that is also a FOREIGN KEY.

CodePudding user response：

So in your case you have a table 'COMPONENTS' that is linked to the table 'PRODUCTS'

You want to have a column in your table 'COMPONENTS' that you'll call 'where_used' that say if the component is used in x or y product.

There is different ways of doing it but this is what I would do :

Basically, you'll need 3 tables :

• The first one is "COMPONENT" which store unique component values
• The second one is "PRODUCT" which store every unique products
• The third one is "COMPOSITION" (or whatever name you like) which is the associated table between "COMPONENT" and "PRODUCT" which will look like this :