Computable Minds -

Why a database can't be designed by anyone?

Posted on: Aug, 13th 2010
Fragment of an entity relation schema
Unfortunately a lot of people, thinks that design a relational database is something very simple and that anyone that have a pair of classes of Microsoft Access in a academy can dedicate to it. But doesn't occur the same when instead of a database the work is about design a flat, an airplane or a car. In all this cases the people knows that needs an architect, an aeronautic engineer and automobile engineer, respectively. But who has the guilty of this way of think?

On the one hand, the majority of the people that use the computer only know the existence of the informatics at user level, and for ignorance they think that is not necessary a professional to do any think related with informatics, because they see all very easy. The level of specialization and the years of study that require some branches of informatics, as databases, is something totally unknown for them, so they don't think about informatics, not even as a profession, when the reality is that is a profession with the same level of complexity as can have the job of a doctor, architect or an industrial engineer.

On the other hand, the guilty is the Microsoft Access itself, that is sell as a tool that anyone can use with the same facility as a text processor like Word, or as a spreadsheet like Excel. Although Access, about its functionality and performance, it is like a toy database management system (DBMS), don't means that design a database with this tool has to be more easy. If you want a good advice, never goes to learn Access to an academy. Learn Access require learn first how to design databases and for that you need several years.

Also of that Access has influenced very negatively at the world of databases because has accomplished change the name to the elements of the relational model. If you know something about databases probably you would have hear the terms: table, registry, field and relation that are names invented by Access. Instead you would have to use the correct names of the relational model that are relation, row, column and foreign key respectively.

Often, in my job, I have found databases of people that happily, knowing little or nothing about databases, have made it them self for their enterprise, to later realize that they needed a professional to develop it. Below I'm going to expose the three most serious fails that normally I found. Any of this fails causes inconsistencies in the inserted data that make these databases serve only to occupy space and lost the time:

1.- Use less relations (tables according to Access) than the necessary: The person that need the application always think that is a lot more simple than in reality is. For a small database that needs about ten relations, the user will think that need one o two and for a big database of 200 or 300 relations the user will think that need ten.

2.- Not using primary keys. All the relations must have always a primary key, it's that to say, a column whose value can't be repeated and in this way identifies unambiguously every row in the relation. Otherwise we will can make things as, insert 1.000 times the same client in the database, without knowing that was inserted already, or that every row that has contact data different for the same client and also don't be possible establish foreign keys in others relations to assign, for instance, the orders that have made that client.

3.- Don't use foreign keys or don't require referential integrity in the foreign keys. That produces incoherencies at insertions, deletions and actualizations. Following with the prior example of the client that make orders, we could insert orders although don't have client assigned, delete clients that have realized a order or assign, at an actualization, a order to a client that don't correspond.

Besides all of this, is habitual that the database it is not normalized, that is to say, it has a wrong design. To check it the normal forms are applied, that are a way to ensure that the design of the database it's right. First we check that it's in first normal form. If it's in first normal form, we check that it's in second normal form and so on to the fifth normal form. Usually a database realized by a non-expert person pass rarely of the first and never of the second.

The design of a relational database is something complex, is the art of adapt the maximum possible the mathematical model of the relational algebra to the reality. This is not always possible to achieve to the 100%, for this reason, sometimes, we have to appeal to the code programming to solve the maladjustments of the reality with the model. To find these maladjustments we appeal to other models more expressive that we go transforming to the relational model implemented by the DBMS. A first design is realized with a conceptual model like the Extended Entity Relationship, later it's converted to a logical model like the relational model and finally it's converted to the physical model of the DBMS that usually it's a relational with certain peculiarities depending on its implementation. Only a person that have designed a big quantity of databases and have studied the fails in every case, can be trained to realize this difficult task.

Also we have to make that the database it's adjusted to the reality of the business and the needs of the client, is something quite complex because it's common that we have to guide the client about what needs and, when know what want, don't match with the things that really needs. This situation also produce that the client begin to change continuously of opinion about that he want during the development process of the product, something that a professional is accustomed to dealing.

Now, knowing this. What do you think? Do you leave the design of databases of banks, medical centres, transport mediums, etc., in the hands of an Informatics Engineer or in the hands o someone else?

Comments (0): Comment
Categories: ,


Copy and paste in your page:

How about you!? Don't give your opinion?

Replying to the next comments:

To check if you are human answer the question correctly:

I don't like this question, change it!

None of these data will be stored.

(Write the e-mail)

Required field.

(Write the e-mail or several e-mails separated by coma)

Required field.

To check if you are human answer the question correctly:

I don't like this question, change it!

Daiatron on Google+