I'm making a rental app which has many categories with unique properties


Category 1
   Car which has
      Fuel, model, color, speed
Category 2
   Apartment which has
      Rooms, lift, generator, floors

I've been thinking of a db architecture for this type of project, but I couldn't find any which could manage this flexible category concept in a performant and scalable way.

  • 6
    Star schema? List of triples? Dumping the properties in as a JSON blob? The trouble here is that the right schema depends on how you want to query the data. – Philip Kendall Oct 13 '18 at 11:40
  • @Philip Kendall thanks for the quick reply actually I thought the same , to store json but while filtering we might get performance issues and I don't think this is scalable or not – T Jagadish Gupta Oct 13 '18 at 11:49
  • 1
    I agree with @PhilipKendall. You're trying to design 10% of an application in isolation, which is never a good idea. The database design should considered with regard to the overall solution. – Dan Wilson Oct 13 '18 at 12:55

I normally go with:

table RentableThings
  Id PK //guid obvs
  Type  //car, flat etc
  CommonProperty  //eg name, date etc

table Cars
  Id PK FK = RentableThings.Id

table Apartment

You then select either from the common table or from the common table joined to the special table as required

| improve this answer | |
  • Thanks for the quick reply so we need to create seperate table for every category ? – T Jagadish Gupta Oct 13 '18 at 12:04

This is a problem as old as the research on object oriented database, since your categories correspond to classes that have different properties. Fortunately, there are many alternatives.

Decomposition of objects into elementary attributes

The most obvious and flexible approach is to make use of an object's identity to decompose it into elementary data that is then assembled/disassembled as needed. You can model almost any kind of categorisation in this way but the assembly/disassembly is expensive. And you'd also need some checks to ensure consistency of stored attributes with the expectations of the object's category/class.

If you go this way, you'd use an object master table of unique object identifiers stored with some metadata, such as for example the object's category/class. The object attributes would then stored separately as a set of triplets of object id + attribute key + attribute value using some kind of key/value access scheme. In an RDBMS different type of values (int, float, string) are in general stored in different triplet tables. A common alternative is to use a no-sql tuple store.

Increasing efficiency and convenience of the decomposition

One variant is to use a different table for each different attribute, with the benefit of indexing on values.

Another varian is to group several attributes in one table. In fact, this variant is what you get using the table inheritance pattern (see below).

Using document stores

A variant is to store an object in a document store. These databases are optimized exactly for this purpose. MongoDB for example, stores json data in a binary bson object, for fast access and reduced overhead.

Using ORM patterns with an RDBMS

Another approach is to use an RDBMS with one of the usual ORM patterns to map different classes to different tables: Single Table Inheritance, Class Table Inheritance and Concrete Table Inheritance usually do the job very well.

| improve this answer | |
  • Thanks for the quick reply , I'll go through your answer – T Jagadish Gupta Oct 13 '18 at 12:05

Do a web search on these two phrases: "Single Table Inheritance" and "Class Table Inheritance". You will find a lot of articles by people who have worked on the same kind of problem before. Some of the articles are based on Martin Fowler's book, Patterns of Enterprise Application Architecture. What follows is a brief summary of what some of the articles say.

Car and Apartment are subclasses of a more generic class, which I'll call RentalItems or just plain Items. if you were to build an object model for this scenario, you would use the inheritance feature of subclasses to do a lot of the work for you. Your app may reflect this.

The Relational Data Model, as such, has no features for inheritance. Some database products have added features to support inheritance, but MySQL is not one of them, to my knowledge. So the problem becomes how to design a system of tables that will mimic inheritance, and make it easy for your app to retrieve the data it needs.

Single table inheritance combines all the rental items into one big table, with many columns (fields) that only pertain to cars or only pertain to apartments, etc. This is quite simple and easy, but it does end up with a lot of NULLS in the data. NULLS can make sifting the data a little confusing and slow.

Class Table Inheritance involves making a single generic table, let's call it Item, for all the rental items. This table contains properties that are common to all rental items, such as description and price. Then there are specialized tables for Car, Apartment, etc. containing specialized data that pertain to each subclass.

You may want to make all the tables, Item, Car, Apartment, etc. use the same shared primary key. This key, let's call it ItemID, is defined in the usual way in the generic Item table. In the other tables, it's declared as a Primary Key, and also as a Foreign Key referencing ItemID in the Item table. When new items are to be added, the app takes care of putting the correct properties in the correct tables, and also of propagating the ItemId from the Item table to one or more of the other tables.

You might want to add views to provide joint views of Car and Item, Apartment and Item, etc. These joint views will run pretty fast for moderate amounts of data, because of indexes that get created when you declare a primary key.

This is just an outline. The articles have examples, diagrams, and the like.

| improve this answer | |
  • Really appreciate for your answer , can you post any references or links – T Jagadish Gupta Oct 13 '18 at 12:08
  • See @Christophe answer for some good links. – Walter Mitty Oct 13 '18 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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