Recommendation on Database Design

[bluesnake]

Hi,
I was seeking for an opinion concerning database design. I’ve got a Company Class having several properties.

A value will be assigned to the property via GUI. These properties are quite static i.e. likely won’t change.

Usually one goes for an approach whereby Property1, Property2, Property3 map into table columns. However what about if we're talking about 20-40 properties? I can envisage 2 options:

IMO possibility 1 is easier to work with while possibility 2 is more flexible. However, CompanyPropertyValue.Value field should accomodate multiple data types??!!.

Any suggestions will be appreciated.

Possibility 1.

CompanyPropertyValue Table

CompanyID int
Property1 int
Property2 varchar()
Property3 decimal
…
Property20 varchar()

Possibility 2

CompanyProperty Table
PropertyId int
PropertyCode
PropertyName

Eg. 1
D77
Turnover

CompanyPropertyValue Table

PropertyId int
CompanyID int
Value

Eg.5
1
100.00

[timothybard]

I would go with something similar to option 2. Option 1 would not be a noramalized database design, so I would stay away from it.

I see you requiring three entities:
1) Company Entity
2) Property Type Entity (this would list the different types of properties for the Company Entity and, if appropriate, a description of the property).
3) Company Property Entity (this would track the company, the type of property and the value for the property).

It looks like that is the setup you mention as option 2, but I wasn't quite such based off of the entity names and field names.

This option has several advantages:
1) Your database is normalized
2) You are not using space in your table to keep fields that are not necessary for each record.
3) Adding additional properties involves only adding records to the second table above and not changing the structure of the company table.

[bluesnake]

Thanks for your reply.

Infact your understanding about the object is correct.
1) Company (main table)
2) Characteristics about the company
3) A table to hold the characteristic value.

Alternative 2 is more flexible.
However I have worked with many applications such as shopping carts whereby the properties of a product weren't held in a table as rows but a columns eg. Colour, width, height, length, name etc.

As for Personal details, designs usually follow a table with Name, firstname, lastname columns.

So why doesn't the same rule apply here?

Which normalization rule am i breaking?
And how to hold data of different data types in one column? -
goes again basic database principles - right?.

[timothybard]

If the properties you are referring to apply to each record, then you can add them as fields (columns). For example, if you are tracking products you are selling, then a height, width, length and weight will apply to each product, so it's okay to have each of those as a field.

The issue comes in if some records have some properties while other records don't have those properties. For example, if you are tracking people and their doctors and some people don't have doctors, then it may not be appropriate to record the doctors as fields in the 'people' entity, but would be more appropriate to track them in a separate entity.

I normalization rule I was referring to was the first normal form, i.e. eliminate duplicative columns from the same table. In your original post, you mention Property1, Property2, Property3. based on the names, they appear to be duplicative columns. However, that is solely based on the names of the fields.

As far as having a field that has different data types... My first recommendation would be to create a seperate entity for each property, e.g, create an entity for width, create and entity for height, create an entity for weight, etc... However, this could result in many additional entities. Another less proper method would be to store all the property values in one entity with a data type of string. From the string values, you can convert to different data types. Again, that is not the most elegant way of doing things, and I wouldn't really recommend it, but it should work.

[bluesnake]

Thanks for reply.

The properties from Property1 to ProperyN are actually not duplicates however they may be categorized into more than one class.

So far the main class is Company. The properties may be divided into ProductionOfCrops and BreedingOfAnimals.

The ProductionOfCrops (20 properties)may be further sub-divided into other classes ProductionOfXXXX. Even if normalization may apply here, it might make sense not to denormalize for performance reasons (A view would be anyhow created to JOIN the X number of tables)...What do you think?.

I believe creating an entity for each property would make the design cumbersome and too normalized to work with....

If the properties you are referring to apply to each record, then you can add them as fields (columns). For example, if you are tracking products you are selling, then a height, width, length and weight will apply to each product, so it's okay to have each of those as a field.

The issue comes in if some records have some properties while other records don't have those properties. For example, if you are tracking people and their doctors and some people don't have doctors, then it may not be appropriate to record the doctors as fields in the 'people' entity, but would be more appropriate to track them in a separate entity.

I normalization rule I was referring to was the first normal form, i.e. eliminate duplicative columns from the same table. In your original post, you mention Property1, Property2, Property3. based on the names, they appear to be duplicative columns. However, that is solely based on the names of the fields.

As far as having a field that has different data types... My first recommendation would be to create a seperate entity for each property, e.g, create an entity for width, create and entity for height, create an entity for weight, etc... However, this could result in many additional entities. Another less proper method would be to store all the property values in one entity with a data type of string. From the string values, you can convert to different data types. Again, that is not the most elegant way of doing things, and I wouldn't really recommend it, but it should work.

[timothybard]

I'm thinking that having three entities may be the way to go to keep things not overly complex while keeping things flexible too:

Company: ID (PK)
Property Type: ID, Description, Data Type, Parent Property Type (?)
Property: ID, Company (FK), Property Type (FK), Value (stored as a string but converted to specific data types on-the-fly through programming).

The Parent Property type is for sub-properties for classification purposes. Perhaps another Property Classification entity could work, but having the Parent Property attribute allows you to nest sub-accounts to as many levels as you need.

[bluesnake]

I took the approach where I created 4 classes to separate classes/tables to store properties as per appropriate class.
This should serve the purpose.

As for custom or adhoc properties the Property and Property table will do the job.

Thanks for your time and insight about this problem.