You are not likely to benefit from a Microservices architecture if all the services share the same database tables. This is because you are effectively tightly coupling the services. If a database table changes all the services will have to change. You have to understand that the whole reason for a Microservices architecture is to … Read more
I usually add ClientID to all tables and go with one database. But since the database is usually hard to scale I will also make it possible to run on different database instances for some or all clients. That way you can have a bunch of small clients in one database and the big ones … Read more
Other answers have ably outlined the pros and cons of your various options. I believe that your Option 1 (property bag) is the best overall design for most applications, especially if you build in some protections against the weaknesses of propety bags. See the following ERD: In the above ERD, the USER_SETTING table is very … Read more
I will summarize my thoughts from my blog post – A lesson in address storage (on archive.org). On my current project [I work for a logistics company] we’re storing international addresses. I’ve done research on addresses all over the world in the design of this portion of the database. There’s a lot of different formats. … Read more
In short : database should enforce constraints. Why : Easier. For ex. to have a constraint set on a particular data column there is only one place to set it : the column itself. The data might come from various sources but the check is put where the data is finally put to rest. Integrity. … Read more
Mythology I always thought that databases should be denormalized for reading, as it is done for OLAP database design, and not exaggerated much further 3NF for OLTP design. There’s a myth to that effect. In the Relational Database context, I have re-implemented six very large so-called “de-normalised” “databases”; and executed over eighty assignments correcting problems … Read more
Assume there’s no scaling penalty for storing all the clients in one database; for most people, and well configured databases/queries, this will be fairly true these days. If you’re not one of these people, well, then the benefit of a single database is obvious. In this situation, benefits come from the encapsulation of each client. … Read more
Facebook.com MySQL with MyRocks. Used to store user info and social activities such as likes, comments, and shares. Hive (Data warehouse for Hadoop, supports tables and a variant of SQL called hiveQL). Used for “simple summarization jobs, business intelligence and machine learning and many other applications” Cassandra (Multi-dimensional, distributed key-value store). Currently used for Facebook’s … Read more
Look in sys.dm_db_index_usage_stats. The columns last_user_xxx will contain the last time the table was accessed from user requests. This table resets its tracking after a server restart, so you must leave it running for a while before relying on its data.
Since Primary Keys are UNIQUE by default, this makes this relation One to One. No, that makes the relation “one to zero or one”. Is that what you actually need? If yes, then then your “second solution” is better: it’s simpler, takes less storage1 (and therefore makes cache “larger”) hes less indexes to maintain2, which … Read more