Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you’re making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn’t necessarily … Read more
Article { “_id” : “A”, “title” : “Hello World”, “user_id” : 12345, “text” : ‘My test article’, “comments” : [ { ‘text’ : ‘blah’, ‘user_id’ : 654321, ‘votes’ : [987654]}, { ‘text’ : ‘foo’, ‘user_id’ : 987654, ‘votes’ : [12345, 654321] }, … ] } The basic premise here is that I’ve nested the Comments … Read more
Why bother? Just store it as a complete date (perhaps always using the first as the day) and use the database functions MONTH() and YEAR() if you only need part of it. This makes using that field much easier as you can still do range queries, etc.
I have done various audit schemes over the years and I am currently going to implement something like this: Person ———————————————— ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, DOB DATE NOT NULL, Email VARCHAR(100) NOT NULL Person_History ———————————————— ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, … Read more
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
There are many ways to store hierarchies in SQL databases. Which one to choose depends on which DBMS product you use, and how the data will be used. As you have used the MSSQL2005 tag, I think you should start considering the “Adjacency List” model; if you find that it doesn’t perform well for your … 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
Other answers have ably outlined the pros and cons of your various options. I believe that your Option 1 (property bag) is the best overall design for most applications, especially if you build in some protections against the weaknesses of propety bags. See the following ERD: In the above ERD, the USER_SETTING table is very … Read more
Splendid question! Firstly, let’s make some assumptions about “better”. I’m assuming you don’t much care about disk space – a bitmask is efficient from a space point of view, but I’m not sure that matters much if you’re using SQL server. I’m assuming you do care about speed. A bitmask can be very fast when … Read more
An age-old problem that has never been elegantly resolved. All the banking packages I’ve worked with store the balance with the account entity. Calculating it on the fly from movement history is unthinkable. The right way is: The movement table has an ‘opening balance’ transaction for each and every account. You’ll need this in a … Read more