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
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
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
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
I would use a ‘link’ concept for all future recurring events. They are dynamically displayed in the calendar and link back to a single reference object. When events have taken place the link is broken and the event becomes a standalone instance. If you attempt to edit a recurring event then prompt to change all … 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
Maybe a bit different approach — supertype/subtype is usually used when you have very specific columns for each subtype, like in Person supertype with Patient and Doctor subtypes. Person holds all data common to people and Patient and Doctor hold very specific columns for each one. In this example your book_notes and article_notes are not … Read more
Survey Database Design Last Update: 5/3/2015 Diagram and SQL files now available at https://github.com/durrantm/survey If you use this (top) answer or any element, please add feedback on improvements !!! This is a real classic, done by thousands. They always seems ‘fairly simple’ to start with but to be good it’s actually pretty complex. To do … Read more