Database design, huge number of parameters, denormalise?

Question

Given the table tblProject. This has a myriad of properties. For example, width, height etc etc. Dozens of them.

I'm adding a new module which lets you specify settings for your project for mobile devices. This is a 1-1 relationship, so all the mobile settings should be stored in tblProject. However, the list is getting huge, there will be some ambiguity amongst properties (IE, I will have to prefix all mobile fields with MOBILE so that Mobile_width isn't confused with width).

How bad is it to denormalise and store the mobile settings in another table? Or a better way to store the settings? The properties and becoming unwieldly and hard to modify/find in the table.

Answer 1

There is nothing bad in storing mobile section in other table. This could even carry some economy, this depends on how much this information is used.

You can store in another table or use even more complicated version with three tables. One is your tblProject, one is tblProperties and one is tblProjectProperties.

create table tblProperties (
id int autoincrement(1,1) not null,
prop_name nvarchar(32),
prop_description nvarchar(1024)
)

create table tblProjectProperties
(
   ProjectUid int not null,
   PropertyUid int not null,
   PropertyValue nvarchar(256)
)

with foreign key tblProjectProperties. ProjectUid -> tblProject.uid and foreign key tblProjectProperties.propertyUid -> tblProperties.id

Thing is if you have different types of projects wich use different properties, you have no need to store all these unused null and store only properties you really need for given project. Above schema gives you some flexibility. You can create some views for different project types and use it to avoid too much joins in user selects.

Answer 2

I want to respond to @Alexander Sobolev's suggestion and provide my own.

@Alexander Sobolev suggests an EAV model. This trades maximum flexibility, for poor performance and complexity as you need to join multiple times to get all values for an entity. The way you typically work around those issues is keeping all the entity meta data in memory (i.e. tblProperties) so you don't join to it at runtime. And, denormalize the values (i.e. tblProjectProperties) as a CLOB (i.e. XML) off the root table. Thus you only use the values table for querying and sorting, but not to actually retrieve the data. Also you usually end up caching the actual entities by ID as well so you don't have the expense of deserialization each time. Issues you run into the are cache invalidation of the entities and their meta data. So overall a non trivial approach.

What I would do instead is create a separate table, perhaps more than one depending on your data, with a discriminator/type column:

create table properties (
    root_id int, 
    type_id int,
    height int
    width int
    ...etc...
)

Make the unique a combination of root_id and type_id, where type_id would be representative of mobile for instance - assuming a separate lookup table in my example.