What is the best way to store a multivalued item

Navigation
1

When issuing a NF-e it is possible to insert up to 990 products / items per document . Since each product has several attributes. What would be the best way to store and link each product, and its respective attributes, to an NF-e?

I currently have the following structure:

Table nfe : 

  'id' int(11) NOT NULL AUTO_INCREMENT,
  'id_nfe' int(11) DEFAULT NULL,
  'id_emitente' varchar(255) DEFAULT NULL,
...

Table tributos_prod : 

'id' int(11) NOT NULL AUTO_INCREMENT,
'id_nfe' int(11) NOT NULL,
'codigo' varchar(255) DEFAULT NULL,
...,
KEY 'fk_nfe' ('id_nfe'),
CONSTRAINT 'fk_nfe' FOREIGN KEY ('id_nfe') REFERENCES 'nfe' ('id')

Problem : As each product / item will have a unique ID, the "infinite" growth of the tributos_prod table would occur, making it unmanageable because Authorize the issue of an NF-e can remove all products linked to this, or even by the simple action of removing a single item.

    
asked by anonymous 03.08.2017 / 23:06

1 answer

1

In the table of items, make a compound key, being: 

nfe (pk=id) (unique= numero, serie, modelo, emitente)
id | numero | serie | modelo | emitente | data_emissao | chave | destinatario | ...



nfe_itens (pk=item,nfe_id) (fk=nf_id)
item | nf_id | produto | valor | quantidade | ...

where the item of nfe_itens is sequential but not AUTO_INCREMENT in this way, each nfe starts the sequence again, item 1, item 2, item 3 ... there goes

There are situations if you use the same table to register a Call, or Guide to a health plan for example, in these cases, I would recommend a key composed of 3 columns, since items may vary between products / services / medicines / among others, then this tipo would be the 3rd column in the composite primary key of the table.

    
03.08.2017 / 23:40
Relation between Python Arrays I can not log into phpmyadmin