It all depends on data volumes and content to tag distribution and density ratios If you have a low tag distribution and density ratio (typical human generated data) you can simply generate an unique id or hash for each possible collection of tags in use by the data. Associate the ‘tag collection’ id with each … 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
To be clear, this question and answer are about using negative numbers for surrogate keys, not for natural keys. As far as I know, there are three reasons for considering it to be a bad practice. It violates the principle of least surprise. Some people assume all ID numbers are non-negative. Some people use negative … Read more
Here’s one way to implement supertype/subtype tables for your app. First, the supertype table. It contains all the columns common to all subtypes. CREATE TABLE publications ( pub_id INTEGER NOT NULL PRIMARY KEY, pub_type CHAR(1) CHECK (pub_type IN (‘A’, ‘B’, ‘P’, ‘S’)), pub_url VARCHAR(64) NOT NULL UNIQUE, CONSTRAINT publications_superkey UNIQUE (pub_id, pub_type) ); Next, a … Read more
I’ll preface this by saying that I rarely delete rows period. Generally most data you want to keep. You simply mark it as deleted so it won’t be shown to users (ie to them it appears deleted). Of course it depends on the data and for some things (eg shopping cart contents) actually deleting the … Read more
What do you mean, C++? Hand coded assembly would be faster if you have a few decades to spare.
You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint). CREATE TABLE T1( Lock char(1) not null, /* Other columns */, constraint PK_T1 PRIMARY KEY (Lock), constraint CK_T1_Locked CHECK (Lock=’X’) ) I have a number of these tables in various databases, … Read more
The best way I’ve found to handle this is to have a floating point order field. When you move something between two other items, set that field to halfway between its neighbors. This is cheap on both reads and writes. The only downside is the floats keep getting longer 🙂
Good on you, for eliminating Nulls. I have never allowed Nulls in any of my databases. Of course, if nulls are prohibited, then missing information will have to be handled by some other means. Unfortunately, those other means are much too complex to be discussed in detail here. Actually it is not so hard at … Read more
I’ll explain by an example: ——————————————————————- | Course | Field | Instructor | Instructor Phone | ——————————————————————- | English | Languages | John Doe | 0123456789 | | French | Languages | John Doe | 0123456789 | | Drawing | Art | Alan Smith | 9856321158 | | PHP | Programming | Camella Ford | … Read more