1

I have ERP database whose good or person entities each have several hundreds of fields. Many of them are used for some subjects only. And hence senior companions (especially from the business division) are quite reluctant to allow me to add new fields when such need arises. E.g. I am not allowed to add such new fields as 'The country of tax residence' or 'Business form', but I am required to seek existing unused fields with the same data type (but with possibly quite different names) and to use them for my new data. I am not happy with this, but I have keep extensive documentation and with it the development process is manageable though it is not nice to work with uncomprehensible names.

As I understand the database practice, then adding new fields whose value is null for the most objects does not increase database size and consumption of resources but it greatly facilites maintainability. However, it increases the size of Java/PHP/JavaScript entities when I synchronize them with the database.

We do not use the notion of inheritance in database. We use Firebird 2.1, 3.0, but, of course, the question can be applied to other databases as well.

Clearly, this is not question about customizable fields, because new fields are used in the main software, there are quite a programming work around them, though this programming concerns only small part of the customers of the main program.

Improve this question
6

However, it increases the size of Java/PHP/JavaScript entities when I synchronize them with the database.

This would be my main architectural concern. If your "Java/PHP/JavaScript" is where your application logic lives, your application entities should be independent of your storage. If your DBAs are adamant they want huge tables, let them do this, but they should consider normalizing the database.

Your queries should not retrieve the entire entity, this is inefficient. For every user table, you might have many user domain objects or DTOs, that handle particular functionality.

Additionally, you could consider querying database views instead of the tables directly. Your views can be concise and act as an interface between your code and your database, and would allow easier restructuring of the database without affecting your code.

Improve this answer
  • 4
    And no, you shouldn't re-purpose fields. One field per semantic meaning, please. – Robert Harvey Mar 18 '19 at 13:48
  • 1
    To expand on this, if you load a good entry with 100 null fields and 8 non-null fields, you don't have to actually retrieve and store all 100 null fields in your application. Instead, retrieve only the fields that you need. If you're only using 5 of the fields, create a view and/or query that loads just those 5. – Clement Cherlin Mar 18 '19 at 17:29
2

I have 30 years of experience with databases in my company. For 20 years we used the same field for multiple purposes when a new purpose was to be rarely used. It was OK when we had HDDs with 500MB. Over time our databases became bigger and bigger, with hundreds of tables and fields. About 10 years ago we realized it is increasingly harder to maintain programs, and especially write updates of database structure because of this practice. Since then we add new fields for new data. We still have not managed to update all multiple usages, and it has been 10 years. I strongly recommend you add new fields for new data.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.