Database schema to track random settings in website for marketing

Question

I have Flex based consumer website where I would like to change various look and feel type settings based on random and other criteria, and then track these through to what results in the most sales.

For instance I might completely switch out the homepage, show different things depending upon where people come from. I might show or hide certain features, or change certain text. The things i might change are as yet undefined and will likely become quite complicated.

I want to design the most flexible database schema but it must be efficient and easy to search. Currently I have a 'SiteVisit' table which contains information about each distinct visitor.

I want to find the right balance between a single table with columns for each setting, and a table containing just key value pairs.

Any suggestions?

Answer 1

Ok, this is very tricky. The reason for me to say that is because you are asking for two things:

• relaxed repository schema so that you can store various data and change what gets saved dynamically later
• fixed database schema so that you can query data effectively

The solution will be a compromise. You have to understand that.

Let's assume that you have User table:

+----------------
| User
+----------------
| UserId (PK)
| ...
+----------------

1) XML blob approach You can either save your data as a blog (big XML) into a table (actually a property bag) but querying (filtering) will be a nightmare.

+----------------
| CustomProperty
+----------------
| PropId (PK)
| UserId (FK)
| Data of type memo/binary/...
+----------------

Advantage is that you (business logic) own the schema. This is at the same time disadvantage of this solution. Another HUGE disadvantage is that querying/filtering will be EXTREMELY difficult and SLOW!

2) Table per property Another solution is to make a special table per property (homepage, etc.). This table would contain value per user (FK based realtionship to the User table).

+----------------
| HomePage
+----------------
| HomePageId (PK)
| UserId (FK)
| Value of type string
+----------------

Advantage of this approach is that you can very quickly see all the values for that property. Disadvantage is that you will have too many tables (one per custom property) and that you will join tables often during query operations.

3) CustomProperty table In this solution you have one table holding all custom properties.

+----------------
| CustomPropertyEnum
+----------------
| PropertyId (PK)
| Name of type string
+----------------

+----------------
| CustomProperty
+----------------
| PropId (PK)
| PropertyId (FK)
| UserId (FK)
| Value of type string
+----------------

In this solution you store all custom properties in one table. You also have a special enum table that allows you to more efficiently query data. The disadvantage is that you will join tables often during query operations.

Choose for yourself. I would decide between 2 and 3 depending on your workload (most probably 3 because it is easier).