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
Yes – you can have a foreign key that references a unique index in another table. CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn) ALTER TABLE dbo.YourChildTable ADD CONSTRAINT FK_ChildTable_Table FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)
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
I do agree with posters below that Options 3, 4, or 5 are most likely to be appropriate. However, each of your suggested implementations has its benefits and costs. I’d suggest choosing one by matching it to your specific requirements. For example: Option 1 pros: Fast to implement. Allows DB actions on custom fields (searching, … Read more
Note on Progression Given the fact that the Question changes substantially (in clarifying the requirement, not is scope) in response to my Response and TRD, this is going to take some back-and-forth. Let’s identify Steps: your Step numbers are odd, starting from 1; mine, in response, are even. Parts of previous Response Steps have become … Read more
I’d go with the Posts approach. This is the best way to ensure referential integrity. If you need additional columns for Answers and Questions respectively, put them in additional tables with a one-to-one relationship with Posts. For example, in MySQL syntax: CREATE TABLE Posts ( post_id SERIAL PRIMARY KEY, post_type CHAR(1), — must be ‘Q’ … Read more
Preface There is an objective truth: Audit requirements. Additionally, when dealing with public funds, there is Legislature that must be complied with. You don’t have to implement the full accounting requirement, you can implement just the parts that you need. Conversely, it would be ill-advised to implement something other than the standard accounting requirement (the … Read more
I think the draft model (following 6NF and 3NF) will help you. I simplified naming convention by removing ‘shop’ keyword. (Also shop entity may leads a separate concept AKA SaaS) SqlFiddle Demo About the questions in the comments: Is it possible to have a unique product ID Yes, it is a common pattern to use … Read more
The three main techniques that is usually applied to the database usage for this kind of a multi-tenant requirement is below. You have already specified some of them. Separate databases for each tenant: very high cost, easy to maintain/customize, easy to tune, easy to backup, easy to code to. Shared database but different schema: Low … Read more
User – Device is a many-to-many relationship, so you’ll want to introduce an intermediary table to resolve that relationship. That table simply consists of two foreign keys, one referencing the User table and one referencing Device. Device – Location can be handled with a simple foreign key in the Device table pointing to a Location … Read more