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
Roles should be tracked separately from user accounts, because someone can be promoted (or demoted) over time. Would it make sense in that situation to have two different user accounts, in two different tables? I think not. Here’s the basic structure I’d use – USERS user_id (primary key) user_name ROLES role_id (primary key) role_name USER_ROLES … Read more
I found a couple of styrofoam models for: Windows 95 and Lotus Notes (if that helps) Actually, here’s a Tom Love case study that shows a couple of his models. This model may represent the least expensive CASE tool on the market — materials cost $20.35. It was more useful than any CASE tools I … 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
Martin Fowler discusses this extensively in his book Patterns of Enterprise Application Architecture book. Get this book and look into: Single Table Inheritance Class Table Inheritance Concrete Table Inheritance The Website should give you some idea. You might also want to read the section on inheritance mappers. Each of the different approaches have their pros … Read more
The sysjobs, sysjobsschedule and sysschedules tables in SQL Server does a pretty good job of this. I wouldn’t reinvent the wheel, I’d just copy their design. Here are some of the important fields from sysschedules freq_type How frequently a job runs for this schedule. 1 = One time only 4 = Daily 8 = Weekly … Read more
Backward chaining (a la Prolog) is more like finding what initial conditions form a path to your goal. At a very basic level it is a backward search from your goal to find conditions that will fulfil it. Backward chaining is used for interrogative applications (finding items that fulfil certain criteria) – one commercial example … Read more
Auto-incrementing primary keys are useful for several reasons: They allow duplicate user names as on Stack Overflow They allow the user name (or email address, if that’s used to login) to be changed (easily) Selects, joins and inserts are faster than varchar primary keys as its much faster to maintain a numeric index As you … Read more
It refers to a “Purchase Order” which typically consists of one or more lines, called “Order Lines”. As almost every business on earth has some similar record of it’s orders, purchases and/or sales, it has been the canonical real-world data example of a Parent-Child (or Master-Detail) relationship for as long as databases have existed (well … Read more
NoSQL databases approach modelling from a different point of view generally. NoSQL modelling usually begins with asking questions on how the data in the database is going to be queried, so the main modelling is ‘what are the list of questions that I want to ask of my database’, whereas with SQL databases, modelling usually … Read more