Data Modeling: Integrity vs. Performance

Navigation
5

In the company I work for, there is a data architecture I've never seen before, and I wonder if this is common, or if it's a new market trend. Just to mention the bank is Oracle. Here are some points:

  • There is almost no relationship between tables, for example, in one of the tables there is a field that represents the id's of another table, but this field is not a foreign key, any value can be saved there. The responsibility for ensuring data integrity is in the API that accesses that database. As soon as a POST arrives, for example, wanting to save in this field, which in my design should be foreign key, it does a select in that in the other table, to verify if this value exists, and if so, then it saves.

  • There are several databases, that is, there is a database for each subject of the company, even though these tables of different banks might very well be related.

  • There is a lot of redundancy. At this point I even understand, because there is the issue of 'denormalization' in favor of performance, but in this case, at least in my conception it is bordering on the extreme.

I questioned the person who did the modeling, and the main argument was regarding performance. Therefore, all responsibilities regarding data, integrity, business rule, are in the API. I started in the job market a short time ago, and I wonder if this is a new market trend.

    
asked by anonymous 03.05.2018 / 16:36

2 answers

1

As you report, a database for every company subject, a lot

19.08.2018 / 07:33
4

If I understand correctly this is the sixth normal form or EAV (Entity Attribute Value). And it is used in some cases. They are using more and more, and in some cases abused.

And it seems to have given up ACID transactions.

It seems wrong to do this in Oracle. If it is to do so probably one of the so called NoSQL is more appropriate. But only seeing the real situation to affirm.

Redundancy by itself is no problem, but may be depending on the case. There are cases that are even mandatory.

In general, what has been exposed should lead to performance problems. Ask him to demonstrate that performance is clear and objectively better. I doubt it, except for redundancy in some cases. If everything is based on this, it may even be. It depends on the read and write load, and the writing patterns.

There's a lot that people exaggerate in using database. It does not always need everything the database has, but yours also seems to be an exaggerated case to the contrary. And I'm going to risk saying that the person followed a cake recipe wanting to make a pie. But I may be wrong. The description of the problem here may be wrong, it may be that the context requires this, anyway, just a speculation.

In fact, it seems to be a trend, a bad one, by the way. My perception is that this is necessary in a very small number of cases. Pragmatists do what they need and almost every problem fits wonderfully well into the relational model, without exaggeration.

Business rule in the application (#, it has no implementation) is quite common and desirable in the vast majority of scenarios. In some cases it is very expensive to put the business rule in the application.

    
03.05.2018 / 16:49
Publishing packages in the NPM repository Float with PHP precision