|
|
Posted by Erland Sommarskog on 06/12/07 21:29
(dsdevonsomer@gmail.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@sommarskog.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
Navigation:
[Reply to this message]
|