I do agree with posters below that Options 3, 4, or 5 are most likely to be appropriate. However, each of your suggested implementations has its benefits and costs. I’d suggest choosing one by matching it to your specific requirements. For example: Option 1 pros: Fast to implement. Allows DB actions on custom fields (searching, … 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
There is a difference between EAV done faithfully or badly; 5NF done by skilled people or by those who are clueless. Sixth Normal Form is the Irreducible Normal Form (no further Normalisation is possible). It eliminates many of the problems that are common, such as The Null Problem, and provides the ultimate method identifying missing … Read more
A quick way of finding the elbow is to draw a line from the first to the last point of the curve and then find the data point that is farthest away from that line. This is of course somewhat dependent on the number of points you have in the flat part of the line, … Read more
I tend towards first approach for all the usual reasons of normalisation. This approach also makes it easier to perform data cleansing on mailing details. If you are possibly going to allow multiple addresses (mail, residential, etc) or wish to be able to use effective dates, consider this approach Customer (id, phys_address_id) Cust_address_type (cust_id, mail_address_id, … Read more
What are the core principles and motivating forces behind the party model? To the extent that I’ve used it, it’s mostly about code reuse and flexibility. We’ve used it before in the guest / user / admin model and it certainly proves its value when you need to move a user from one group to … Read more
I dont know if this is the best way to do this, but since I got no ideas from anyone else, this is what I would be doing. I hope this answer might help others as well. We have 2 tables notification —————– id (pk) userid notification_type (for complexity like notifications for pictures, videos, apps … Read more
Assuming you don’t store things like the ‘+’, ‘()’, ‘-‘, spaces and what-have-yous (and why would you, they are presentational concerns which would vary based on local customs and the network distributions anyways), the ITU-T recommendation E.164 for the international telephone network (which most national networks are connected via) specifies that the entire number (including … Read more
I believe that in practice, using a natural key is rarely the best option. I would probably go for the surrogate key approach as in your first example. The following are the main disadvantages of the natural key approach: You might have an incorrect type name, or you may simply want to rename the type. … Read more
One item is going to have many tags. And one tag will belong to many items. This implies to me that you’ll quite possibly need an intermediary table to overcome the many-to-many obstacle. Something like: Table: Items Columns: Item_ID, Item_Title, Content Table: Tags Columns: Tag_ID, Tag_Title Table: Items_Tags Columns: Item_ID, Tag_ID It might be that … Read more