In Search of a Standard Model Database design
I've always been criticized for keeping my neutral position with regard to design, model selection, patterns, styles and prototypes of all that concerns the programming and databases.
But this time I break a little ice proposing a "standard" for modeling databases.
is not the objective at this time to select a database, which will at a later time.
But first assume that already have a basic knowledge of databases, especially in regards to standards, and has worked at least one database.
is unlikely that anyone has ever wondered why the title, if it is a great background in the world of databases, possibly identify the following quote:
Datamining: The art of torturing the data until they confess
But how could "confess" a poorly organized and structured data? An almost impossible task.
After experimenting with several proposals of different authors, models and "standards", I venture to propose a somewhat risky and daring, and for many counterproductive and abusive, wanting to clarify that my goal is not to say that is a panacea or a statement that must be taken immediately, it is simply a given that everyone will be implemented as deemed. As
develops different entities or tables, with the main problem that I faced is to make / maintenance is the allocation of names to the tables and their fields.
There are several proposals to designate names for tables and fields, which I list below, in addition to presenting the pros and cons of each.
- Notation 1:
- Rules:
- The name of the table must have a maximum of 12 characters and all letters must be uppercase. (Ex: Student)
- The first three letters of each field must refer to the table, followed by a dash of ground or soil (_) and then three characters to indicate the contents of the field, and all characters must be capitalized. (Eg EST_COD, EST_NOM, EST_APE, EST_DIR)
- Advantages:
- For your time ofrececía main advantage was that due to the limitation that had the MS-DOS and similar files only up to 8 characters guaranteed that the name always would be valid. It never applied to databases on Unix or similar. SQL Code
very short and relatively easy to modify - Disadvantages:
- may occur if two fields may have very similar names and complicate the maintenance of the tables
- Notation 2:
- Rules:
- All table and field names are capitalized
- The name of the table should be the least amount of characters and this is all the vowels deleted. There are only two exceptions to this rule. The first if the word starts with a vowel, and the second, when the vowels of last syllable is meaningful or can clarify the word (eg, ESTDNTE)
- The field names must start with three characters, corresponding to the table where they belong or, in the case of foreign fields, the name of the master table (Ex: EST_CDGO, EST_NMBRE, EST_APLLDO CIU_NCMNTO)
- Advantages:
- organized tables and fields, where you can define the data source in the case of foreign fields.
- Disadvantages:
- resulting SQL code extremely complex and confusing, due to the possible similarity between words, they contain no vowels difficult to read and well maintained appropriate database
two proposals which are very similar but mutually exclusive, as you will note below, but are intended to provide an alternative to current design standards, design and development of databases.
Start with a common basis for both proposals: Proposal
design and conventions for databases:
- The names of both entities and attributes (tables and fields) must be written in capital letters. Characters may be used [AZ] [az ][_][ 0-9]: This is done because of various limitations of databases particularly old, so you should avoid using special characters for compatibility, but if you have a database with support for different encodings exist, of which I personally recommend the UTF-8, since it is universal and is governed by the UNICODE standard. I recommend that you use special characters, and these greatly facilitate the reading of each of the sentences, but must take into account each of the limitations of databases and especially changes that generate SQL statements, therefore, wear but considering that this requires minor changes in the generation and special creation of SQL queries.
- The name of an entity or a field can not start with numbers: Although at present it is possible, you should not even think it does not show any case where required, if any, would love know, but until that happens, you should not do.
- The name of the entity or field, in the event that is composed of two or more words, they must be separated by an underscore (eg personal_departamento)
- An entity name can be composed of two parts: a prefix of some characters that indicates the scope of use of the company or its principal membership either a subject or module and a name that represents the identification of the entity, united by an underscore
- For any case the names of the fields or entities, should ensure that they are not too extensive, leaving aside the expressiveness and clarity
- names names of entities and fields should be written in the singular and for them to avoid the use of verbs
- fields that are key primary entity must be denoted with the prefix or suffix ID, and the name of the entity without prefix (if any) joined by an underscore preferably.
- foreign key fields and primary keys with which they relate should be of the same name, except when an entity has more than one foreign key to the same primary key. In this case will be an appropriate name that represents the relationship.
- Boolean fields but should be avoided by using lists, if any, should be in a participle in the case of verbs or use any prefix or suffix that identifies them as such, separated by an underscore
But regardless of any theory, standard or rule, the general rule for the development, design and implementation of databases is in a primitive simple enough.
If anyone who sees the design can define the operation, logic and meaning of each of the entities and their relationships can be considered that the work has been completed successfully
