I’m working on a project utilizing notifications as well, I’m not sure if you got yours sorted out by now or if it might help but this is the table structure I used: Notifications: – ID (PK) – recipient_id – sender_id – activity_type (‘comment on a post’, ‘sent friend request’, etc) – object_type (‘post’, ‘photo’, … Read more
If you need to allow NULL values, use a UNIQUE constraint (or index) instead of a PRIMARY KEY (and add a surrogate PK column – I suggest a serial or IDENTITY column in Postgres 10 or later). Auto increment table column A UNIQUE constraint allows columns to be NULL: CREATE TABLE distributor ( distributor_id GENERATED … Read more
This question is a bit old but I decided to answer it anyway since I have been doing some digging on the same. My answer is based on the linux file system. Basically mySQL stores data in files in your hard disk. It stores the files in a specific directory that has the system variable … 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
This MSDN article comparing the two is for SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx For most purposes, there’s no difference – the constraint is implemented as an index under the covers. And though there’s the ability to disable the constraint, it doesn’t actually work in SQL Server. It only matters if you want to tweak things like … Read more
If a “source” doesn’t “send an identifier”, the column will be unchanged. Then you cannot detect whether the current UPDATE was done by the same source as the last one or by a source that did not change the column at all. In other words: this does not work properly. If the “source” is identifiable … Read more
Yes, use Like. A query such as: Select id from sometable where name like ‘%abc%’ would return any row that contained “abc” anywhere in the name column. If the pattern you are looking for happens to contain the % or _ character, you can use the ESCAPE keyword to define an escape character to include … Read more
While not as useful for a simple plan like this, http://explain.depesz.com is really useful. See http://explain.depesz.com/s/t4fi. Note the “stats” tab and the “options” pulldown. Things to note about this plan: The estimated row count (183) is reasonably comparable to the actual row count (25). It’s not hundreds of times more, nor is it 1. You’re … Read more
Why not use identities on all the tables, but any time you present it to the user, simply tack on a single char for the type? e.g. O1234 is an order, D123213 is a delivery, etc.? That way you don’t have to engineer some crazy scheme…
ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON)