Creating different tables for every record

Frappe Framework
,
1

Hi Guys, I need some advice from you on one of my projects. So, this is a cataloging app being developed using frappe framework.

Concept:
There are few masters: Item Type Master, Item Master, Attribute Master and Serial Number.

Item Type Master: This tells the type of item say, gold or silver etc.

Attribute Master: defines all possible attributes such as weight, width, height, length, volume etc. These is a exhaustive list of all the attributes and all of them may not be assigned to the items.

Item Master: defines and item with item name, say, plate or idol. Here plate may have diameter and weight as attributes and idol may have volume, weight and dimensions as an attribute.

Serial Number: this is a singular unit of the item as every instance of item will be unique.

Everything should be configurable for end user. Such as adding new attributes and assigning to one of the records of item doctype.

Now, I have a two part question:

  1. How can I create new database for every record in item doctype? Is it even possible? I am afraid as I will have around 1 million serial numbers and having it in the same table ta serial Number will not be a good idea.

  2. If not possible, how can I utilise attributes assigned to each item?

Any suggestions will be of much help.

2

Hi @nimish:

Maybe you would need some attribute template, and assign it to your Item Master.

Item attributes are static? For example, every individual gold plate (with different serial number) has its own weight, diameter, etc …? Or all the gold plates has the same values for attributes?

3

@avc thanks for reply. Attributes are not static, that is, every item may have different attributes and attribute values.

What do you mean by attribute template?

4

Hi @nimish:

So … sorry if i didn’t fully understand … but every “instance” (unique piece) of gold plate item has its own attributes and values?

I’ve imagine something like this:

ITEM TYPE MASTER

GOLD PLATE
SILVER PLATE
GOLD IDOL
SILVER IDOL

ATTRIBUTE MASTER

WEIGHT
HEIGHT
VOLUME
DIMENSION A
DIMENSION B
DIMENSION C
…
…

ATTRIBUTE TEMPLATE

GOLD PLATES (WEIGHT, DIMENSION A, DIMENSION B)
SILVER PLATES (WEIGHT, DIMENSION A, DIMENSION B)
GOLD IDOLS (WEIGHT, VOLUME)

ITEM MASTER

GOLD PLATE MODEL A01 (TYPE: GOLD PLATE, ATTRIBUTE TEMPLATE: GOLD PLATES)
GOLD PLATE MODEL A02 (TYPE:GOLD PLATE, ATTRIBUTE TEMPLATE: GOLD PLATES)
SILVER PLATE MODEL C01 (TYPE: SILVER PLATE, ATTRIBUTE TEMPLATE: SILVER PLATES)
…
…
SILVER IDOL MODEL D01 (TYPE: SILVER IDOL, ATTRIBUTE TEMPLATE: SILVER IDOLS)

ITEM MASTER ATTRIBUTE VALUES

GOLD PLATE MODEL A01 / WEIGHT: 2KG DIMENSION A: 20CM DIMENSION B:20CM
GOLD PLATE MODEL A02 / WEIGHT: 3KG DIMENSION A: 20CM DIMENSION B:20CM
SILVER PLATE MODEL C01 / WEIGHT: 3KG DIMENSION A: 20CM DIMENSION B:20CM
SILVER IDOL MODEL D01 / WEIGHT: 4KG VOLUME: 24cm3
…
…

SERIAL NUMBER

3093098239/ITEM TYPE: GOLD PLATES
3589098409/ITEM TYPE: GOLD PLATES
4887987498/ITEM TYPE: SILVER PLATES
…
…
…
5985498498/ITEM TYPE: SILVER IDOL

This way just need to create a template for attributes, and assign it to each item, and set values. It would be useful if you have many similar items.

You will need a child table to store values for attributes. In my example, all the “instances” of the same item has the same attributes and values (all units of GOLD PLATE MODEL A01 has the same weight and dimensions). In this case, this child table should belongs to ITEM MASTER doctype.

If each “instance” of the same item has different values, the child ATTRIBUTE VALUES should depends on SERIAL NUMBER.

Sorry for this novel :man_facepalming:
Hope this helps.

5

Hi @avc, thanks a lot for all your efforts in helping me.

Apologies for unable to communicate clearly.

In my case serial number is like design number/model and there will be only one piece per design number, so one serial number per designs and multiple designs/serial numbers per item. So I guess mine is a second case.

So, Do you think having a million records in serial number table and filtering them on every pagination makes sense? Does it have impact on performance? Can we create seperate table for serial numbers of each unit of an Item?

Also, I am bit confused in creating template and assigning it to an item. How can I set field type etc. Can you please elaborate?

Thanks :sweat_smile:

6

Glad to help.

Don’t worry, I am not english fluently spoken so I often missunderstand things …

You can manage this volume of data but … take care of hardware, performance, indexing, database tuning, etc … Explore if PostgreSQL can fit your requeriments better (only if you will not use ERPNext). Anyway, there are no easy way (AFAIK) to manage separate databases for this … Framework is based on 1 site → 1 database strategy, so I can’t see benefits.

Just suggesting to group attributes and apply to item as a template. This way you will not repeat the same attributes over and over again …

Hope this helps.