Best practices for DaDaBIK applications development, part 1/2
Hello everybody,
during the last years, I have been involved in the design and development of some big and complex Web applications created with DaDaBIK.
They are BPM (Business Process Management) applications that typically:
- automate some enterprise business processes / workflows in order to improve efficiency
- provide analytics / dashboards to keep monitored some aspects of the business.
While they are not open to the public (typically they are internal applications), they might have thousands of users that log-in daily to accomplish their jobs and quite big databases where all the related information is stored.
I would like to share with you what I consider some best practices when it comes to develop these kind of applications. In this first blog post, I will only talk about database design suggestions, in a second and last blog post I will talk about all the rest.
So the first rule is the following: spend an appropriate amount of time designing your database before starting the DaDaBIK configuration.
A lot of problems I see on complex DaDaBIK applications depend on poorly designed database. I know that sometimes you just get a legacy database and you have to deal with it but maybe the creation of a new application is the right moment to do some adjustment/refactoring. Here are my suggestions:
- Choose the right field types: this help data integrity and makes the DaDaBIK configuration faster (DaDaBIK uses the field type info to automatically set some parameters in form configurator). So, it sounds obvious but if you know that a field will only contain integer numbers, set it as INT (or SMALLINT, BIGINT, …) !
- Choose the right field names: if you are consistent with field names, you will never wonder how you called that particular field when you are adding some custom code, you just know it. It can also help the DaDaBIK configuration, not only because you immediately recognize the goal of your fields in the form configurator / permission managers but DaDaBIK also uses the names as an additional information (have you ever noticed that if you have a field called email_customer DaDaBIK automatically chooses email as content type?).
- Set referential integrity constraints (foreign keys): again, this helps both data integrity and DaDaBIK: for a foreign key, DaDaBIK will automatically set a lookup field, without additional manual step in form configurator (this automatism, however, only works with MySQL at the moment).
- Indexing: this can help a lot with the performances of your final application. There is a lot to say about indexes but if you don't know much about the topic, here is a general and simplistic rule that works most of the time: if your users, for a table X, frequently search or sort for a particular field Y, you should index such field.
- Normalization: for this point, there would be a lot to say, but in a nutshell:
- For each object (or, better, entity) of your application, create a table (e.g. products and customers are two entities, you need two tables)
- If you have a 1-to-many relationship between two entities, add the primary key of the first table as an additional field of the second table and set the corresponding referential integrity constraint (foreign key); e.g. for each customer you can have many invoices, for each invoice one customer, so in the table invoices, you add the field id_customer as foreign key.
- If you have a many-to-many relationship you need to create a new table to represent it, e.g. each author can write many books, the same book can be co-authored by many authors so you create a books_authors table having as fields id_author (foreign key) and id_book (also foreign key); the two fields, together, should be the primary key of the table books_authors (check, however, what I say in the last point "use auto-increment fields").
- If you end up having a table with a huge number of fields, this might be the sign that something in the design of your db was wrong. This is not a rule, if your fields all describe properties of a specific entity, that's fine; however what I have seen several times in real world with legacy databases is that a table schema having many columns is the result of a bad design and the table can be split in two or more tables .
- Double check that you are actually using UTF-8 character encoding.
- Use auto-increment fields as primary keys. This is not a general database design advice, this is a DaDaBIK specific advice. This might change in the future but at the moment there are some features, such as after insert hooks or (insert) revisions that correctly work only if your table has an auto-increment field as primary key. Furthermore, if you have a table having a composite primary key (such as a table derived from a many-to-many relationship, see normalization before), consider that DaDaBIK cannot use it as unique field so you need to add an additional auto-increment field (for MySQL, this can be done automatically).
I hope this first set of simple rules can help you!
Best,
Eugenio Tacchini
DaDaBIK founder