DB Design: Varying structure or normalisation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DB Design: Varying structure or normalisation

Author

Topic

nickjones
Starting Member

16 Posts

Posted - 2004-01-20 : 07:50:53

Once again I call on you guys for your expert knowledge. I've got a question on a database design.

I'm designing a windows software application running off a local database. The software deals with Files, and each file has multiple Properties. The properties are user-defined. The software may come with properties set up as 'Size', 'Type', 'DateOfExport' etc, but the customer can come along and add their own field types.

I can see two ways of doing this:

Modifying the DB Structure
I have one table - tblFile, with each property represented as a field in the table: FILE_ID, FILE_Path, FILE_Size, FILE_Type, FILE_DateOfExport etc.

Then when the user adds their own property, it modifies the structure of the table and adds in the new field.

Or
Normalised Database
We have 3 tables:

tblFile:
FILE_ID - FILE_Name
-----------------------
1 somefile
2 someotherfile

tblProperty:
PROP_ID - PROP_Name
-----------------------
1 Size
2 Type
3 Date Of Export
[...]

tblFileProperty:
FP_FileID - FP_PropertyID - FP_Value
-------------------------------------
1 1 20 kilobytes
1 2 GIF File
1 3 13/01/2004
[...]

Now I'd much rather go along the normalised route, but I think it poses more problems.

The main issue is pulling out the information. Say I run a query to pull out all the files and all the properties, something like:

SELECT tblFile.FILE_Name, tblProperty.PROP_Name, tblFileProperty.FP_Value
FROM (tblFile INNER JOIN tblFileProperty ON tblFile.FILE_ID = tblFileProperty.FP_FileID) INNER JOIN tblProperty ON tblFileProperty.FP_PropertyID = tblProperty.PROP_ID

the return would look like:

FILE_Name PROP_Name FP_Value
-----------------------------------------
somefile Size 20 kilobyes
somefile Type GIF File
somefile Date Of Export 13/01/2004

etc. The program logic would then have to run through all this to display it in a grid format (one row per file, properties as each field). With the first method, it would be a simple select and would return it how I like. For thousands of files with 20+ properties this is going to make a big difference to the speed of the software.

The second problem is that properties could be integers, dates or text fields - so the FP_Value field would have be set to a varchar to allow all field types. This is rather unresourceful, and I'll run in to problems with the properties are used in the SELECT (to get only those with date property between these dates etc).

Any suggestions on how to go about this are most appreciated.
Sorry for the long post, and I hope it makes some sense... :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-20 : 10:12:15

If you go the first route, I don't think it's too horrible of an idea -- but you need to break things into 2 tables. 1 is your main "FILE" table which will NEVER change, and your entire structure of your app and all stored procs will reference and use that table. Then, you create another table called "FILE_ATTRIBUTES" or something like that, and in THAT table you can add/remove columns.

In the second method, however, you have some good options. First off, you can have 3 "value" columns in your table -- one for text values, one for numeric, one for dates. And in the field definitions of your custom fields, you can specify which datatype each field is.

And whenever fields are defined, you could dynamically alter a view which cross-tabs all user-defined fields into 1 row, selecting the proper value from the proper datatype column. Something like:

User_Defined_Types (PK of datatype)
-------
DataType (Date, Numeric, Text)

User_Defined_Fields (PK of fieldname)
-------------------
FieldName
DataType

User_Defined_Values (PK of filenumber,Fieldname)
-------------------
FileNumber
FieldName
DateValue
TextValue
NumericValue

and then your view would be built with pseuocode something like this:


ALTER VIEW UserDefinedValueCrossTab AS
SELECT FileNumber,
  <% for each field in the UserDefined Fields Table>
       <%if dataType = 'Numeric'%>
       SUM(CASE WHEN FieldName= <%fieldname%> THEN NumericValue ELSE 0 END)
       <%if datatype = 'Date' %>
       MAX(CASE WHEN FieldName= <%fieldname%> THEN DateValue ELSE Null END)   
       <%else%>
       MAX(CASE WHEN FieldName= <%fieldname%> THEN TextValue ELSE Null END)
       <%end if%>
       AS <%fieldname%>
   <%next field%>
FROM
  USer_Defined_Values UDV
GROUP BY FileNumber

you would run that ALTER VIEW statement as User defined fields are created or dropped.

just some ideas ... but never ALTER your main "Files" table, use a 1-1 related table for user defined fields if you do go the first route.

- Jeff

nickjones
Starting Member

16 Posts

Posted - 2004-01-20 : 10:55:58

Thanks for the response. Both ideas are good.
I think I'd prefer the second, because then I know all versions of the software would have exactly the same structure - less problematic when the user upgrades the software, for example. Also I don't really like the idea of having code to add, delete and modify columns in the code. Could get messy.

So I think I'll go along the second route - with the user defined view. Do you think there'd be much of a performance hit over the first method?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources