Beyond normalization forms

Hello.

I’m stuck on the following problem. Let’s imagine you want to create a DB where you can’t have a clear data specification. For example the following table

===================

#Building ID | Location

===================

And your customer has the great idea to add a not foreseeable column called GPS Infrared Satellite Control string (Who could have thought of that!!!)



===============================================

#Building ID | Location | GPS Infrared Satellite Control String

===============================================


Fine, BUT there can be NOBODY who can change the Database. You see or customer has many such bright ideas, and we can afford to change the database every time based on his new ideas, but either can we make him to give us an exact data definition or tell him to keep his ideas for himself.


WHAT TO DO NOW?

The key to solve this problem is to change the database only by inserting new rows into predefined normalized tables.

Any idea how to approach such a solution, or where to begin start looking for something like that?

As said, Normalization Forms can’t deal with this form of unforeseeable data.

Thanks for your time reading this.

I'm not quite sure I understand your problem... but here it goes.

I gather you have a table Building with an id and several predefined columns.
Now you have a customer who wants to add new columns and you can't handle that in your code...
(wich sugests to me you're using * instead of columnames in select and insert statements)
What you could do is define a table with the names of the extra fields your contractor wants and then define a table that has a foreign key to the building table and a foreign key to the fields table that holds all the extra values
eg
CREATE TABLE Buiding(
buildId INTEGER NOT NULL IDENTITY(1,1)
, buildLocation VARCHAR(100))

CREATE TABLE ExtraFields(
fieldId INTEGER NOT NULL IDENTITY(1,1)
, fieldName VARCHAR(100))

CREATE TABLE BuidingFieldValues(
bfvId INTEGER NOT NULL IDENTITY(1,1)
, fieldId INTEGER NOT NULL
, buildId INTEGER NOT NULL
, bfvValue VARCHAR(100))

extra fields would then have a record say with fieldId=1 and fieldName='GPS Infrared Satellite Control String'
and in buildingFieldValues you can store records with fieldId=1, buildId=1 and bfvValue='whatever' and fieldId=1, buildId=2 and bfvValue='whatever else'

This alows you to create a standard application and then tailer it to a specific clients whishes

Hope this helps

Thanks for your reply.

I thought the same way like you, but found the following disadvantages. Let’s keep our example and your suggestions.

Table Buildings
===================
#BuildingID | Location
===================

Table ExtraFields
===================
#fieldID | fieldname
===================

Table BuildingsFieldsValues
=======================
#fieldID,#BuildingID,bfvValue
=======================

As we can see our tables are in 3NF, but taking a closer look at the table BuildingsFieldsValue we have a MVD (multi-valued dependency) so to solve the table for 4NF we split the BuildingsFieldsValue in three tables lets say:

BuildingsFieldsValues1
#fieldID,#BuildingID

BuildingsFieldsValues2
#fieldID,#bfvValue

BuildingsFieldsValues3
#BuildingID,bfvValue

Theoretically everything should be fine now, but splitting our primary key #BuildingID & #fieldID created unspecified values. Which means now we can’t have queries that give us the desired value for a specific fieldID and BuildingID.

At this point, excuse me for writing a whole story, but I think it’s necessary.

So we forget about 4NF, which brings the following problems

Our table BuildingsFieldsValue would have data like

BuildingID fieldID bvfValue

1 ------- 1 ----12,4

1 ------- 3 ----Wall street

1 ------- 4 ----GPS Infrared Satellite Control String

2 ------- 1 ----15,4

2 ------- 3 ----Park street



And so on. We are wasting disk space for repeating all the time the Building ID (but who cares with GB of available disk-space). The main problem is that the database has tables with an average row number of 2.500.000-2.700.000. Searching the BuildingID column and the fieldID column means approximately doubled processing time, when having unsorted columns which will be the fact.

This can be a problem; because the database is online available and high user traffic is expected so update, select and insert queries will take more time when using the above solution.

Using log tables, which save the incoming traffic can make things better, but it takes time the server to update this tables and also it can be that there is no idle-time on the server for such processes. Having a separate server for this task can solve things but SERVER = $$$MONEY$$$.

Any idea how to solve the above described problem with the tables, or where to start looking for a log-table solution? Some related articles or books would be also very useful.

That was all; I hope it was interesting to read.

Thanks for your time.

The real solution to your dilema is to have clear and concise application requirements before any code, etc... is started.

Without this you next best option is to have Generic blank fields (in most tables), where the Customers can populate anything they want in them, but can't really perform any searchs against them, unless they want to pay for customized programming.

Yes if takes up more HD space, but "with GB of available disk-space"

S-

Thanks for your interest.

As said before in the post, we can’t have “concise application requirements” and customized programming. The goal is a fully-customizable and stand-alone database, with a satisfactory performance as described in the above post. Any idea for a good google search?

"Concise Application Requirements" is exactly what any well developed/designed application should have.

Requirements define the Who/What/When/Where and How of an application.
They should be clear and concise.

Reality, It isn't going to happen with out a ton of coding and then your performance is going to go down, and then you actually lose the flexibility you are trying to gain (system becomes code dependent instead of data dependent).

Give them some extra dummy fields in each table and leave it at that.

Good Luck

S-