Store data that can be queried outside the database itself, goes against the 1st Normal Form?

3

For example, I have an invoice, which I can check in another database (but not mine), an example would be PagSeguro. I only store the invoice ID number so I can check it.

There is no doubt that storing this data that can be queried would conflict with database normalization , more specifically, with 1FN - 1st Normal Form?

    
asked by anonymous 06.01.2017 / 13:40

2 answers

4

Storing in a separate table in your database would not violate the first normal form. This normalization imposes nothing that the information should be somewhere external, it refers to the relational model of its database. Storing on the same table will be a violation.

The question is whether the result will be as expected. Can the data in the external resource change? What happens if this happens? Do you want the data in your database to be updated as well, or ideally to remain as it was at the time of use? If it is the latter option, how does it resolve if the external information is used again is different? I doubt that's the case with an invoice, but I do not know how they operate.

Regardless of the answer, do what you need to do. Do not be concerned if it is in the normal way or not. It is good that you are, but if you have reason not to follow the normal way, do not follow, it is not mandatory. If you start doing it because someone said you should always do so, you will be doing it wrong. Do what is right for your project. Denormalizing is interesting too .

I do not know the exact case in detail, but I would probably bring the data to my base so I do not depend on external resources. Depending on the case there may be a need for a verification mechanism if the data is up to date.

    
06.01.2017 / 14:02
3

No. The first normal form dictates that you can not have multivalued columns, and that's not what you're having. Relevant topic for further clarification: What is database normalization?

Once you store the invoice number to be queried, this is analogous to a foreign key. This does not constitute (at least not by itself) any violation of some normal form, let alone the first normal form. Despite this, it is still possible that you may be violating some normal form depending on where or how you store it.

In addition, the concept of normalization applies to the relational database. Your case is not a relational database, at least not purely, but a distributed base for web services. Therefore, you can not devise a SQL query that joins including table data from your invoice, because PagSeguro's webservice does not work like this, and another approach would be needed. In addition, since data is kept in separate places and controlled by separate entities, there is no guarantee that there will be referential integrity .

    
06.01.2017 / 14:01