Database Design Guidelines

Question

I am pretty good at SQL Server, MySQL, Oracle, etc., but putting these database products aside, is there a resource that will help me build relational databases well? Is there something like templates or best database design techniques?

I have seen several times that the database often does not scale; people have personal preferences with preserving columns, for example the isChecked column, which is logical in nature, but saved as Char (1) with values ​​like "Y" and "N" instead of 0 and 1, which sound better to me. Ways to not make common mistakes when designing a database?

Links to books or articles will be highly appreciated.

Thanks in advance.

Answer 1

A few points:

• Find out as much as possible about the problem domain . You cannot create a good data model without knowing what you are designing for
• Have good knowledge of the data types provided by your database provider.
• How to use normalization and design tables
• Performance: when and how to apply indexes , how to write efficient queries , etc.
• When and how to use different database objects, such as views, procedures, functions, triggers

Answer 2

There are many database design patterns. They are not often beautifully designed, so you might just have to take a look at the many database designs.

See, for example, Fowler 's books on design patterns. Also Nock Book .

There are blogs such as a database programmer .

There is an IEEE book, Based on the design and implementation of template-based databases .

The Google search engine () showed 24M images.

Answer 3

My attitude to this is somewhat opposite. I would advise not to particularly emphasize the design of the database.

Sometimes it can be tricky. With internal LOB applications, the prevailing view of the business is often that DATA is the main asset, where, since the software is somewhat consumed.

My advice: do not buy it.

In fact, an asset is a company's ability to INTERACT with data. To view it, manage it and make decisions based on it.

This means that even if they can put a lot of value into the data, what they actually value is the software that you write.

This means that I will focus on creating an effective user interface, rather than on "developing an ideal database." A database is just a tool that allows you to execute a user interface.

A key feature of relational data models is data and access independence. You can add columns, change keys, enter or delete indexes, etc. Having zero effect (or close to zero) for applications that use it.

This makes the database structure extremely flexible.

Trying to create a database “agile in the future” or “optimize performance” is basically a wasted action.

Changing the database structure will have a relatively small impact on your system.

In addition, you really cannot predict how the database will scale until you come across scenarios where you need to scale. It’s best to wait until you run into performance issues. and then contact them on purpose.

However, changes to the user interface of your application are usually more expensive. The user interface is time consuming and usually takes some time to get right.

So, I would recommend you:

• Just create a crappy database design
• Respond to real-life performance scenarios you encounter
• Focus on the user experience, not the database.

Answer 4

To counter Dilly-O's advice. I would suggest that you do not put all your searches in one table. In general, this is an attempt to force the design of OO into a relational database. This can be done, and it is consistent with the worldview of the OO developer, but this leads to a distortion of database designs.

Give up on Google and find “MUCK Tables,” which will lead you to a discussion of spreadsheets with massive unified code. Alternatively, you can search for “one true lookup table” for discussion. Or even read Joe Selco's article One True Lookup Table .

Answer 5

As with everything, the answer here is: "It depends."

Databases can be used to do different things, and some of these things will require opposite directions in design and development.

The OLTP database system will be developed in a completely different way than the system used as a reporting or warehousing solution. The first is often normalized, and the warehouse is often deformed. This helps the system obtain the desired performance for the intended behavior.

Even within this segment, depending on whether the use is excessively heavy or heavy for recording, various design decisions may be required.

It is best to look at best practices for a much smaller segment of database development that matches the type of application you are trying to build.

Answer 6

The best book I've ever read about database design is Michael J. Hernandez's Database Design for Mortals. The name sounds like a book for beginners, but people at any level can get from this knowledge. It is also platform independent, as it reviews the data itself and its proper organization, rather than the technology used.

He also wrote a book about writing queries called “SQL Queries for Mere Mortals,” which I heard (not yet read) by itself well.

Database Design for Mortals

Answer 7

Do not save calculated values

Example. You have a Squares table with a Width column. No need to create "area" columns, because this can be calculated by width ^ 2

Answer 8

A relational database is an extremely powerful abstraction; it is a collection of facts and a predicate calculus. In addition, SQL enables the separation of command requests with one statement for checking strings and another for changing strings.

When you think of a database as a mechanism of truth logic, it makes sense to have a setting that prevents inconsistencies from flowing from the data you are modeling. Therefore, in order to effectively use a relational database, you need to get the database design correctly. Unlike designing object-oriented programs, there is a consensus on how a relational database should be created. The right approach to database design normalizes how reasonable it is. Most people normalize to the third normal form, but in fact you can go to the fifth normal form.

If possible, you want to remove the null column values ​​from your database. If you agree with my idea of ​​the database as an engine of the logic of truth, then zeros are a real problem. When you have zeros in the database, the law of excluded mean is not satisfied. This makes it difficult to “prove the contradiction” of any given database property that it will be without zeros. Zeros unnecessarily complicate the semantics of the database.

It is sometimes necessary to break normalization rules for performance reasons. However, do not do this before you have data about which queries in particular are slow. Often, you can simply speed up a query by carefully changing the indexes rather than denormalizing them.

Finally, the word stored procedures, not direct queries. In a decent database, you can set security permissions for stored procedures regardless of base tables. This in itself is a sufficient basis for the widespread use of stored procedures. Using stored procedures, you create a tighter security model than is possible with direct SQL access.

Answer 9

I did not find what I was looking for in this question, but this one has a bunch of recommendations for template design in database design

Answer 10

Perhaps the best known best practice is database normalization. This set of methods allows you to design your database so that redundant items are deleted and the fields are grouped logically.

Answer 11

if you are not documenting the enumerations in the schema description column so that I can understand what “5” is:

Select name from peeps where accountStatusId = 5 

then do it

Use the table to list the field. eg:

 Select name from peeps p join accountStatus s on p.accountStatusID = s.asid where s.accountStatus = 'ActiveDude' 

Answer 12

Michael J. Hernandez Book Design of a database for mere mortals is well written and easy to read. He must answer all your questions.

Hernandez is also a co-author of SQL Queries for Mortals with John L. Viescas.

Books are about $ 60 apiece. I am trying to find a CD for queries for mere mortals because I lost mine. If anyone has a copy, let me know.

Answer 13

I would say that as long as the database is normalized, and if you create VLDB, then divide it correctly, then everything will be fine. other best practices include using CRUD for stored procedures and ensuring that all tables are cascaded correctly. most of the rest is subjective. Using "Y / N" is programming the old school database from the moment the bit has not yet been entered. It can also be used for scaling purposes such as "Y / N / Maybe", but if that were the case, then bast practice would say to normalize this and make a lookup table.

Answer 14

One concept that we use here that has been found to be quite enjoyable is the Lookup Code table. If you have a database in which there are many links to elements that are efficiently codes or types, or the like, save them all in one LookupCode table, which bases its functions on the CodeGroup and the code itself.

We keep an additional flag for the active state of the code, as well as several optional numeric columns that can be used if the specified search code needs to be sorted or calculated in any way.

By doing this, you eliminate tons of small little tables scattered around your pattern. Now, one of the disadvantages of this is that the main key for the table is the code group and the code itself, so there is no foreign key attached to the "master" table, which refers to this code, but a slightly forced application in the application is easily adapted for this.