User configurable table designs

[dsdevonsomer@gmail.com]

To the pros:,
I have one question about designing tables and interface (web/windows
applications). The requirement is like this.
I want to design an application to manage upload / review / manage
files which has some meta data associated with them as well. The files
can be any documents. This application can be used in different
companies same way, but each company wants to use different field
names.

So, I want to design tables generic enough to allow custom labelling
fields. So if I have a table

T1 with ID, FileName, FileSize, FileAuthor.... some company may want
to use different field names. What's the best way to design tables
like this and how to maintain relationships, stored procs, triggers
etc linked to these tables.

Many thanks in advance,
DS

[Erland Sommarskog]

Re: User configurable table designs

(dsdevonsomer@g mail.com) writes:

To the pros:,
I have one question about designing tables and interface (web/windows
applications). The requirement is like this.
I want to design an application to manage upload / review / manage
files which has some meta data associated with them as well. The files
can be any documents. This application can be used in different
companies same way, but each company wants to use different field
names.
>
So, I want to design tables generic enough to allow custom labelling
fields. So if I have a table
>
T1 with ID, FileName, FileSize, FileAuthor.... some company may want
to use different field names. What's the best way to design tables
like this and how to maintain relationships, stored procs, triggers
etc linked to these tables.

Why would your clients want to have a say on how you name your table
columns. This is a very dubious requirement, and one that could be
very costly to implement and maintain.

It may be that clients want to attach extra attributes to the documents
which are not in your basic design. This can be handled by an EAV design.
(Entity-attribute-value). That is, you have a subtable with the columns (ID,
Attribute, Value). Preferrably another table defines the attributes, and
the permitted domains for the attributes.

While sounds neat, beware that EAV can be very problematic it you need
to run queries against it. If you can define the desired attributes as
columns, do so.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx