0

In my PostgreSQL database, I need to define the following data structure. There will be a table of item, and it has an auto-incremental column item_id and other item specifications. When a new item is entered, the item_id will be incremented by 1. This is for a parent item.

There will also be child items which have the same item specifications and their unuqie item_id. Multiple child items can be associated with a parent item. The first child's item_id is defined as {parent_item_id}.1 and the second child is {parent_item_id}.2, so on and so forth.

A parent item can be associated with multiple child items hence forming a "one-to-many" relationship.

pseudo Parent Item
item_id, item_name, attribute1, attribute2... attributeN
1, item_name1, attribute, attribute, ..., attributeN
2, item_name2, attribute, attribute, ..., attributeN
3, item_name3, attribute, attribute, ..., attributeN

Please note item_id for only the parent items needs auto-incrementing by 1.

pseudo Child Item
item_id, item_name, attribute1, attribute2... attributeN
1.1, item_name, attribute, attribute, ..., attributeN
1.2, item_name, attribute, attribute, ..., attributeN
1.3, item_name, attribute, attribute, ..., attributeN
2.1, item_name, attribute, attribute, ..., attributeN
2.2, item_name, attribute, attribute, ..., attributeN
3.1, item_name, attribute, attribute, ..., attributeN

What is a best practise to structure the data? Is it a good choice to make two separate tables for parent and child, plus a relationship table?; or make parent and child in one single table?

alextc
  • 3,206
  • 10
  • 63
  • 107
  • . . I don't think there is a "best practice". It depends on what the entities represent. – Gordon Linoff Jul 25 '18 at 11:16
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. This is also too broad, you are asking for a book or chapter. – philipxy Jul 26 '18 at 05:32
  • Possible duplicate of [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) – philipxy Jul 26 '18 at 05:35

2 Answers2

0

I suggest to use parent and child records in a same table. as part of well normalized design, use column with parent_item_id. If Item is parent one then parent_item_id will be NULL else respective Item_id.

How to track parent item with all child records?
Use Recursive CTE to get all records from single Item_id only.

JERRY
  • 1,165
  • 1
  • 8
  • 22
  • Thanks. the item_id for parent items are auto-incremental by 1 but the child items aren't. How to deal with it? – alextc Jul 25 '18 at 23:39
  • All items will be auto incremented by item_id, either it is parent or child one. – JERRY Jul 27 '18 at 04:15
0

No need to create auto-incremental reference for parent_item_id. Please refer below example.

CREATE TABLE item_mst(item_id BIGSERIAL, Item_name VARCHAR(100), parent_item_id BIGINT, is_active BOOLEAN, created_at TIMESTAMP)

For inserting Parent Item:
INSERT INTO item_mst(item_name, parent_item_id, is_active, created_at)
VALUES('Books',NULL, TRUE, NOW());

For inserting Child Items:
INSERT INTO item_mst(item_name, parent_item_id, is_active, created_at)
VALUES('Book_1',1, TRUE, NOW()), ('Book_2',1, TRUE, NOW()),('Book_3',1, TRUE, NOW());

select * from item_mst;
JERRY
  • 1,165
  • 1
  • 8
  • 22