Here is how I ended up solving this problem. I decided to use a dictionary to store data that is unique to each notification type. I then serialize that dictionary object into a binary string and store that in the database along with each notification. I have a template assigned to each notification type that … Read more
Definitely store them in the main table with a column to indicate whether the data is approved or not. When the change is approved, no copying is required. The extra work to filter the unapproved data is the sort of thing databases are supposed to do, when you think about it. If you index the … Read more
Given that the badge criteria can be arbitrarily complex, I don’t think you can store it in a database table broken down into “simple” data elements. Trying to write a “rules engine” that can handle arbitrarily complex criteria is going to take you down the path of basically re-writing all the tools that you have … Read more
I would prefer “Clients_Brokers” (pluralizing both names to indicate many-to-many).
A few points: Learn as much as you can about problem domain. You can’t create good data model without knowing what you’re designing for Have good knowledge about data types provided by your database provider How to properly use normalisation and design tables Performance: when and how to apply indexes, how to write efficient queries … Read more
I think it’s a general rule of thumb to stay away from mystical bitstrings that encode the meaning of the universe. While perhaps clunkier, having a table of possible permissions, a table of users, and a link table between them is the best and clearest way to organize this. It also makes your queries and … Read more
No. In some other uses, there are some advantages to use structures with a power of two size, mostly because you can fit a nice (power of two) number of these inside another power-of-two-sized structure. But this doesn’t apply to a DB fieldsize. The only power-of-two-sizing related to VARCHARs is about the exact type of … Read more
Take a look at Martin Fowler’s Patterns of Enterprise Application Architecture: Single Table Inheritance: When mapping to a relational database, we try to minimize the joins that can quickly mount up when processing an inheritance structure in multiple tables. Single Table Inheritance maps all fields of all classes of an inheritance structure into a single … Read more
For me, the main thing is a decision whether to use the OrderedPartitioner or RandomPartitioner. If you use the RandomPartitioner, range scans are not possible. This means that you must know the exact key for any activity, INCLUDING CLEANING UP OLD DATA. So if you’ve got a lot of churn, unless you have some magic … 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