Flexible Catalogue Management System... is there anything I am missing?

I am creating a catalogue management system for my Dads company. The current one, which I have designed and launched over the past couple of years, is good and efficient but it has its limits in the sense that if he wants to add a property to an item type then I have to add it in the database and code behind and relaunch the system.

Now, for the next major version, I am trying to modify this so that he can define all the columns himself - so I have an ItemType table where he names the item types; an ItemTypeProperty table so that he can define all the columns and their datatypes, an Item table where all the items are stored with the ID of their ItemType and an ItemPropertyValue table which uses an ItemID, ItemTypePropertyID and stores the value. The aim is to enable him to say that he wants a column of type so that, if there are two ItemTypes called Range and Collection, he can say that he wants Collection to be a property of Range.

Has anyone here faced the creation of this kind of system before and, if so, is t开发者_高级运维here anything major I am missing or anything I may want to consider before I get too much further? I mean like some kind of problem I may hit later which I could avoid by doing something now? Alternatively are there any technologies which would be of a lot of assistance? I have tried Linq but found it hard to customise it to my needs at the time. I am currently using VB.Net 2.0 (the limitation set by the server; not under my control) with Sql Server 2008. I have created my own system derived from the MVC kind of architecture.

Thanks in advance.

Regards,

Richard

Edit:

My new database structure is:

Table: ItemType

• ID
• Name

Table: ItemTypeProperty

• ID
• ItemTypeID
• Name
• Description
• DataType

Table: Item

• ID
• ItemTypeID

Table: ItemPropertyValues

• ID
• ItemID
• ItemTypePropertyID
• Value

What are the main architectural drivers here? How important is maintainability? How would you compare them to to runtime needs like performance?

This might sound kind of corny - but having a clear idea about what's important and what's not will help you make good decisions.

The reason I say this is because systems where users can define what would 'normally' be something more rigidly set in the application are implicitly more complex and therefore more challenging to work with. Basically it's a trade-off: some tasks will become easier - others harder.

On the other-hand it's not uncommon to find systems like this - so if you haven't done one before you'll definitely find it a worthwhile experience. All I'm saying is that if the current design "works" then you should only change it if there is a good reason to.

Specifics:

• Getting the data model right will be important. Before you go too far make sure you understand the 5th Normal Form: basically you will (probably) run into issues where two concepts that can't be linked in the real-world can be in your system - because they're all values (ItemTypes) in the same table (this link might be better).
• Reporting will be harder - because you don't have a nice flat table structure to work-off, so you might also get less tool support (just a hunch - totally unsupported opinion).
• You might find yourself putting 'more' constants into your system - if you do just make sure you map them out relatively well in advance (it related to getting the data model right).

Update:

"It seems to work but I won't know until I have multiple ItemTypes and instances of those"

This exactly the point I wanted to make about data modelling. By data modelling I didn't so much mean physical construction of the DB, but a thorough understanding of the underlying data - the "logical data". Starting with pen & paper / a white board is a great first step; from that you might well progress into doing some design work using one or more databases to actually try things out before committing to a full system.

"If a system of this size can be as efficient as it is then why can't this?"

You're probably trying to compare apples and pears. Some reasons:

• Hardware and configuration. I'm guessing your system and this one aren't exactly the same.
• Architectural priorities: At a guess I'd imagine that this site was built with performance in mind; it's certainly not something you can add as an after thought, however, that doesn't mean you can't improve the performance of an old system - you certainly can - although it's a specialized skill.
• Caching, Pre-Calculation: using caching is a fairly obvious way of improving performance. Another is to pre-calculate data in advance of it being requested. This is something you may want to consider.