We are trying to design a database which will be used to store meta-data for the configuration of a programmable switch. The configuration UI presents an object model of the switch. Each configuration object has a set of properties. Each property has a set of meta-data which will be stored in one or more tables. There are over 100 configuration objects in the system, each containing 5 properties or more.

Now for the question. Is it better to use separate tables for each object/property/meta-data combination or to store similar pieces of meta-data for different object/property combinations in the same tables.

Here is a simple example.

We have a configuration object A with property B which can have 5 possible values one, two, three, four, five.

Should these possible choices be stored in a unique table:

A_B_Choices having five rows (one per possible value).

Or would it be better to store these choices and other choices for this object and other objects in one large table which would include identification information such that we could easily find the choices for object A property B.

The concern we are having is that with 150 objects already identified, we can see well over 1000 tables storing this kind of information.

Sorry for the noob question but we don't really have a DB expert on our team and keep going back and forth on this issue without a clear or obvious choice.

Thanks,
lou

single table is by far the best option

lcordero, I'm not certain whether you are really talking objects at all. I think you may mean classes. A class is the type of an object and an object of any class always has the same set of properties.

If you plan to represent each property as an attribute in your database then it makes sense to have one table for each unique combination of attributes - ie one per class rather than one per object. Push attributes up the hierarchy so that they appear only in the minimum number of tables, ie attribute A should appear in the table for the parent class which has A in all of its subclasses.