Which is preferable, a table-relationship or a few additional ones. fields?

Question

Database

For example, there are several types of articles on Habré (draft, translation, tutorial).
What is preferable, to add additional fields isDraft, isTranslate to the table with the article... but what about additional fields for different types? For example, the author who was translated and a link to the original page? Add translateAutor, etc., but there will be a lot of unnecessary information ..
Or make an additional table and connection?
Table of articles + table of connections + table of properties.
The first option is convenient in programming, direct property access based on AR or simple direct queries, everyone is happy...
Second option is complex AR: Article->property->value, complex joins in direct queries.

Answer 1

Depends on what you prioritize.
If performance, all the same as a result you will come to partial denormalization.
Your example with translateAuthor - if there are a lot of requests for articles, then it makes sense to store the author's name directly in the article table so as not to follow it with a separate request.
And if a little, then to hell with it. Do you feel? Everything depends on the task.
And then there's PostgreSQL and hstore ;)

Answer 2

Practice has shown that eventually complex and universal property / value solutions will still develop to caching values ​​in fields nearby (it will be perfectly normal to fill half-empty cache tables with 100500 fields with triggers).
Those. for speed, you'll create these fields anyway (otherwise relational databases are oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooollylly slow otherwise), but having a property/value approach frees up your hands and gives you more options in the future.

Answer 3

As experience shows, when using a relational database, flags (isXYZ, etc) add much more problems than benefits. For document-oriented databases, there is no such problem. You can also look towards table inheritance in relational databases (for example, in Postgres).

Answer 4

If you want to understand the code, then normalize according to the third form.
If you want speed, then first look at the table engines. If the engines are the same, if the fields are of the same type, if there are keys for binding fields, then several tables can be used - the speed loss will be minimal. If any of the conditions is not met, then it is better to use a single table + processing by the shell code.

Answer 5

For example, there are several types of articles on Habré (draft, translation, tutorial).

If additional properties for each entity are not planned, then I would make flags. If planned, then select the entities in separate tables and link them by the article key. In the reference table, put a unique key on the id of the article to eliminate duplicates.
If speed is critical, combine 3 reference entities into 1 table with a full set of required types.
If there are many different sets and directories - then EAV or make your own table for each. EAV is more flexible, but with large data, you will have to do denormalization and / or caching, as rPman wrote above . Otherwise, you will have to spend time describing each new entity. If the project allows, then I would choose this option.

Answer 6

Make a link table. So it will be more academic, more correct and it will be possible to refine later.