One table per every similar object?

Question

I write an application for drawing shops. I have these classes in my system: shop, cart place, rack and bakery.

They have this properties:

shop: X, Y, name, width, height, type, address

cart place: X, Y, name, width, length, type, capacity

rack: X, Y, name, width, length, type, height, balance_limit

bakery: X, Y, name, width, length, type, open_hours

Now I would like to represent these classes in my database. But as you can see all classes from above have same things like:

X, Y, width, height, name and type. And what make them different is:

shop: address

cart place: capacity

rack: balance_limit

bakery: open_hours

I know that in future all these types of object will have their own new properties and that they will gain new properties that all of them will have at once.

And I know that there will be new types of objects not mentioned above that will have some properties same as shops, cart places , racks and bakeries.

I would like to create database structure that will allow me to add new properties and new objects. And to add new properties that will be added to every class in the same time. And moreover I would like the system to be clearly designed and allow me to do easy database queries to it.

So my question is:

Should I make a database table for every type of object (shop, cart place, rack, bakery) because it will be more clear or should I combine it all together into one table because they have similar list of properties?

I would like you to give me some idea why one solution will be better than another. I hope to get some practical advice here not only "you should do it, because it's only right way, axiom".

Answer 1

Yes, you should use a single table for each object as they are their own entities. If you map these tables to objects you will eliminate the need to join multiple tables making things more efficient.

Also each Object is then isolated in terms of development and complexity.

Answer 2

What benefit do you get from 'sharing' a table for the common items?

If none, then don't do it - just stick them in different tables (especially if they will diverge further in the future).

I guess you are not using an ORM?

Answer 3

This is not an easy question... SQL databases are not good at modelling class hierarchies.

You will need a good ORM.

What I put a class hierarchy in a table, do is this :

First I make sure it is pertinent : for instance, putting Nodes, Articles, etc for a web CMS in the same table makes sense because these are all variants of the same thing.

The idea is that you need to create database columns for searching, indexing, and making SQL queries, but you don't need to store all your information in database column. You can store the rest in a serialized object in a BLOB column.

In the table, there are - of course a column that indicates which class this row is an instance of - a few "core" column that are common to all classes, basically the base class fields. - other columns that are used only by some subclasses, but that I need for searching, so they need to be indexed - a BLOB that contains all other data from the object.

Basically when I store an object in the database, the relevant columns according to its class are filled, and the rest of the data (or even the entire object) is shoved in the BLOB.

The great thing about this is, if you add a member value that doesn't need to be searched or indexed, only stored, you don't need to put it in a database column, so you make no changes to the database at all : it will be stored in the serialized BLOB. The only thing to do is add a default value for this member in your deserialization code, so the objects from this class which are already in the database and do not have this member will have a decent default value for it.

You can also version your object formats if you like, it gets more complex.

However this scheme has some drawbacks :

Constraints are difficult to apply : - you can only apply constraints to fields that have a column. - since some columns only occur with some classes, the database needs to know a bit about your class hierarchy.

For instance you will probably want to put the addresses in a separate table, and add the relevant fields (zipcode, country, street, number, etc) : putting all this in your main table would add too many columns. Also you will, at some point, want to add some Customers or other things that are in a different table and also have an address, so it is better to put address in a separate table and REFERENCE them.

Same thing for people or companies, etc.

Now a shop has an address but a cart does not, I presume, so you will have to express in database DDL that a row from your table must REFERENCE an address if it is of type "shop" but not of type "cart".

It can get a bit hairy.

Also if you have 10 shops and 100.000 carts, for example, it may be interesting, for performance, to split the tables, so you get a nice small fast table and one big table.

---

Now there are other solutions :

For instance, you could put all the code and base members in the base class, but make the tableName a class attribute that is changed in the derived class. In this way, just changing the table name, all code applies to a different table, but you do not have to rewrite any of it.

Then you get 1 table per class.

You can of course apply the method above on each table if your class hierarchy gets more complex.

---

How to choose between the two ?

Basically if you make a web CMS and you store in a table, objects of classes derived from Node like : - Article - Image with a legend - Gallery - etc

All these objects are basically the same stuff. They will all have a Title, a TextContent field, belong to a ParentNode, etc

If you make a keyword search for "foo" in the TextContent, it is much easier if all objects are in the same table.

If you want to list all children of a ParentNode to display them on a web page, it is also much easier if everything is in 1 table.

So in this case the first method is really a benefit.

Now in your case, the objects are not that similar.

Personnally I would not even give them the same Base Class. I would create a Mixin names "ThingWithCoordinates" (maybe something shorter) and add this to the classes.

Now, maybe a Bakery is close enough to a Shop that it can inherit from it, but carts and racks, probably not.

In your case I would definitely use several tables. And in each table, if you need to store several classes, I would use the first method.

Most important is that your class hierarchy (and thus tables) must be based on something RELEVANT (car dealers and bakeries are shops) and not common features that happen to exist between objects that actually have nothing else in common (like cart and shop). For this, there are mixins to share common code, but not base classes.

Answer 4

What I suggest :

1. Design your domain model properly, without any regards to database issues. Entities sharing a property (name, for instance) does not mean they are in any way related. Though they could well be...
2. Map this design to a database structure, choosing well known Object-Relational Structural Patterns (cf. Database Design).
3. Develop your product using a proper ORM solution (preferably one that allows your underlying database structure to be modified afterwards).
4. In the case you run into performance issues, consider (de)normalizing your database to fix the problem.

Answer 5

Search the web on "generalization specialization relational modeling".

You will find several good articles on how to design your SQL database when this pattern arises. The best of the articles follow your criterion of providing guidance rather than laying down canonical rules.

Answer 6

If the commonality is something similar to shop_size then I would suggest creating a separate table for that.

The reason is that by normalizing you can get other information, so, for example, it makes it easy to have a drop down showing width and length as there may be many shops with the same measurements.

You can also look at the data that is in this table to get other information, later.

Mainly, you get flexibility, IMO.