A database design question

Question

Hi!

I have to store on a database four kinds of object on a database. These objects have the same atributes:

• Name (varchar2).
• Description (varchar2).
• File (Binary blob).

Maybe I can use a table to store these objects adding a column to identify the kind of object but I need to store a lot of objects (> 1,000,000 or much more).

My question is Which scenario is better to improve performance? A table to store all objects or a table for each kind of object.

I'm going to use SQL Server 2005 or 2008.

Thank you!

Answer 1

If you have only four different type of objects and they are all of about the same size and number, breaking the table will not do too much. You can reduce the cost of a table scan by the factor of four, but you do not want to be doing full scans anyway. You will go through an index, and then it does not matter.

If the four types are completely different in size or number or frequency of access, separating them might benefit the smaller, less numerous. more frequently queried ones. But this needs to be a big skew to have an impact on performance when using an index.

If you do decided to break up the table, it gets more difficult to query across multiple types, or to add a new type later.

On the other hand, if you never need to query across multiple types (and the name is not unique across all object types), there is no need to keep them in a single table.

I notice that you do not have a "type" column. You should probably have one if you need to tell the four types apart. Or can this be done just by looking at the name?

Is the name a primary key? Table size has only a very small impact on performance for primary key lookups.

Answer 2

Pure volume is not a good reason for splitting up similar objects into different tables, there are other and much better ways to improve performance, indexing, table partitioning.

Add a type column to your table and the maintenance and your queries will be easier.

Answer 3

Create one master lookup table with the name and id for the different types of objects. Then create the dependent table with id instead of name. You can have a single table and partition it horizontally according to the object type (id).

The benefit of having an integer id instead of a name is that you can create an index on the same which will speed up your queries considerably (specially with the size of the table being what you mentioned)

Answer 4

Your question was about performance, not convenience. Therefore, a separate table for each would be best; this reduces the number of records in each index, and you are essentially doing a filter yourself by selecting the correct table.

Answer 5

Are the objects genuinely equivalent or just coincidentally similar? By lumping them all together you might be making unjustifiable assumptions. Later on when you decide that one of the object types needs additional attributes that the others don't need you might end up with a sizeable re-factoring task or end up with sparsely populated rows.

Also be suspicious of any field called "description", it's a bad smell, indicative of under modelling. It tends to get used as a catch all for missing attributes. I don't advocate over modelling but a lot of useful structure can get buried this way. For example I once had to add some functions to a product database that did some processing based on "territory approval". But there was no territory approval attribute??? after talking to users it became clear that they stored the territory data in the description field using a system of special codes that they had devised.

Answer 6

what is the purpose of database design