Ausgangspunkt und Zentrum der Arbeit am Altägyptischen Wörterbuch ist die Anlage eines erschöpfenden Corpus ägyptischer Texte.
In the early twentieth century one might have created a card index to study a large textual corpus, but in the twenty first one is more likely to rely on a relational database instead.
Generates the following sql in sqlite3: "SELECT \"patients\".* FROM \"patients\" INNER JOIN \"users\" ON \"users\".\"id\" = \"patients\".\"user_id\" WHERE (\"users\".\"name\" LIKE '%query%')" And the following sql in postgres (notice the ILIKE): "SELECT \"patients\".* FROM \"patients\" INNER JOIN \"users\" ON \"users\".\"id\" = \"patients\".\"user_id\" WHERE (\"users\".\"name\" ILIKE '%query%')" This allows you to join with simplicity, but still get the abstraction of the ARel matcher to your RDBMS.
Object hierarchies are very different from relational hierarchies. Relational hierarchies focus on data and its relationships, whereas objects manage not only data, but also their identity and the behavior centered around that data.
Delegated types newly introduced here looks like a Class Table Inheritance (CTI).
A very visible aspect of the object-relational mismatch is the fact that relational databases don't support inheritance. You want database structures that map clearly to the objects and allow links anywhere in the inheritance structure. Class Table Inheritance supports this by using one database table per class in the inheritance structure.
I suggest to make it UNIQUE because it seems like the column should be unique
I think you should normalize if you feel that introducing update or insert anomalies can severely impact the accuracy or performance of your database application. If not, then determine whether you can rely on the user to recognize and update the fields together. There are times when you’ll intentionally denormalize data. If you need to present summarized or complied data to a user, and that data is very time consuming or resource intensive to create, it may make sense to maintain this data separately.
When to normalize and when to denormalize. The key is to think about UX, in this case the factors are db integrity (don't create errors that annoy users) and speed (don't make users wait for what they want)
Can database normalization be taken too far? You bet! There are times when it isn’t worth the time and effort to fully normalize a database. In our example you could argue to keep the database in second normal form, that the CustomerCity to CustomerPostalCode dependency isn’t a deal breaker.
Normalization has diminishing returns
Now each column in the customer table is dependent on the primary key. Also, the columns don’t rely on one another for values. Their only dependency is on the primary key.
Columns dependency on the primary key and no dependency on other columns is how you get 2NF and 3NF
A table is in third normal form if: A table is in 2nd normal form. It contains only columns that are non-transitively dependent on the primary key
3NF Definition
Relational databases are designed around joins, and optimized to do them well. Unless you have a good reason not to use a normalized design, use a normalised design. jsonb and things like hstore are good for when you can't use a normalized data model, such as when the data model changes rapidly and is user defined. If you can model it relationally, model it relationally. If you can't, consider json etc.