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
You need to think of how you approach the application in a document oriented way. If you simply try to replicate how you would model the problem in an RDBMS then you will fail. There are also different trade-offs that you might want to make. ([ed: not sure how this ties into the argument but:] … 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
You create a separate table for phone numbers (i.e. a 1:M relationship). create table `users` ( `id` int unsigned not null auto_increment, `name` varchar(100) not null, primary key(`id`) ); create table `phone_numbers` ( `id` int unsigned not null auto_increment, `user_id` int unsigned not null, `phone_number` varchar(25) not null, index pn_user_index(`user_id`), foreign key (`user_id`) references users(`id`) … Read more
A. Preliminary Your Approach First and foremost, I must commend your attitude. It is rare to find someone who not only thinks and works from a solid grounding, and who wishes to understand and implement a Double-Entry Accounting system, instead of: either not implementing DEA, thus suffering multiple re-writes, and pain at each increment, each … Read more
What I’ve seen done, and what I currently use are embedded arrays with node id’s in each document. So document user1 has property groups: [id1,id2] And document group1 has property users: [user1]. Document group2 also has property users: [user1]. This way you get a Group object and easily select all related users, and the same … Read more
There actually is conceptual reasoning behind both styles. Wikipedia on the relational model and graph databases gives good overviews of this. The primary difference is that in a graph database, the relationships are stored at the individual record level, while in a relational database, the structure is defined at a higher level (the table definitions). … Read more
Suppose you have a primary key containing a nullable column Kn. If you want to have a second row rejected on the ground that in that second row, Kn is null and the table already contains a row with Kn null, then you are actually requiring that the system would treat the comparison “row1.Kn = … Read more
If all the candidate keys of a relation contain multivalued attributes: Introduce a surrogate attribute for at least one multivalued attribute. For each attribute you deem “composite” (having heterogeneous components, like a tuple): For each attribute component that can be missing: Add a relation with attributes of some multivalue-free candidate key and an attribute for … Read more
There is no conclusion that composite primary keys are bad. The best practice is to have some column or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row. SQL (and the relational model) allows a composite primary key. It is a … Read more