At present I am working in a startup company. There I am involved in both development and in database design. For this project we are using MySQL. In future we may migrate it to noSQL things like MongoDB or some other based on the traffic. So here my doubt is what should we keep in mind when designing a database, that should be fit for both RDBMS and nosql databases?

I know RDBMS and noSQL are entirely different. But what points should be considered when designing a database? How to give a good database design which will suit for RDBMS and noSQL?

  • Encapsulate the details of the database in a layer (set of subroutines) between the application and the database. Try to build the API in some minimalist way to try to avoid any knowledge of even whether it is RDBMS or noSQL. It will be difficult. – Rick James Aug 22 '16 at 4:05
  • Can you explain clearly.I did n't get you.Which API I have to use?.And also I have another doubt In RDBMS we will apply normalization on tables ,but if we apply normalization it will be difficult in document type database(MongoDB).If the tables are in denormalized state then it will be easy to migrate.With out relationships and normalization creating a database design is not good,but if we apply those things it will not suite for Document type databases.Then how should i design a database design for my project? – Vijay Raju Aug 22 '16 at 5:32
  • Your application invents a new API for talking to the code that talks to the database. – Rick James Aug 22 '16 at 17:37
  • The lack of normalization is a deficiency in noSQL. – Rick James Aug 22 '16 at 17:37
  • This is a "religious" debate. I feel that noSQL is fine for toy implementations, but you need RDBMS for real scaling. I should shut up now. – Rick James Aug 22 '16 at 17:39

The database designs for relational and document stores will be entirely different. You will not be able to take one and simply implement it in the other.

What will endure, however, is the data model - the logical representation of the entity types, connections between them, cardinalities, constraints, attributes, domains and rules governing them. The data model represents what your customers know and how they act. The database is one physical representation of this, limited by the product chosen and hardware. Getting the model right and keeping it up to date will aid current development and smooth any future transition.

The data model is a logical construct. It should not, in theory, be affected in any way by the features of the chosen storage product. From one data model several database designs can be derived. For a relational storage product the translation is quite direct. An entity type from the data model becomes a table in the database. A relationship from the data model becomes a foreign key in the database. Take the example of an order processing system. The entity types "Order" and "OrderLine" become the tables Orders and OrderLines, with OrderLines having a foreign key column of OrderNumber.

For a document store the translation may be more complex. The designer has more choices to make. Order and OrderLine may be mapped to different collections, they may be mapped to different documents within the same collection, or order lines may become sub-documents nested within the order document. The relationship between the logical entity types Order and OrderLine can be implemented either by copying the order's number into the order line's document, having an array of the order line's IDs in the order document or by nesting the order lines as sub-documents within the order document. Which to choose will be determined by the application's requirements. Whatever the physical implementation the logical analysis in the data model remains constant: one Order, many order lines.

The next obvious point is to be scrupulous in separating to data access tier from the others. This way future rework will be focused on that area. Having good test scripts will pay off now and will be doubly valuable after the rewrite, to speed regression testing.

Finally, the chance of you migrating is slim. I do not doubt you will be successful. I do doubt you can guess now what your business and technical environment will look like several years from now. Everthing I have read by successful software companies says they rewrite the product three times before they get it "right." Use one of those re-writes to change persistence product and concentrate now on delivery and customer service.

| improve this answer | |
  • Then how should I give a database model to my project.I am in a confusion which rules i have to follow? In RDMS we will apply normalization on tables and associations as well.But as if I follow those rules it will be difficult in document type databases after migration because their no foreign keys and normalization concept .plz help me how to start my design ??? – Vijay Raju Aug 22 '16 at 5:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.