Managing a record-field relationship through SQL

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)

Managing a record-field relationship through SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-21 : 07:42:40

Geoff writes "Hi,

I've been looking around in all sorts of places for an answer to this, and I'm surprised that I haven't managed to find anything on the subject. I'm a programmer who isn't particularly skilled in SQL or SQLServer, and for the solution you have complete control over schema/types/sprocs - there's no integration anywhere here and I'm not afraid of changing things. SQLServer is the main priority for developing this, but I'd eventually like to be able to use any database so any tips on how the solution would work on other DBs would be great.

Here goes:

I want to be able to store a 'record' in SQL Server, and this 'record' can have multiple 'fields' associated with it. So, a record could be, say, an Issue record that has fields for Author, IssueTitle, StepsToReproduce, and IsClosed.

The problem is that I want this to be a generic mechanism that will allow you to have any number of fields associated with a record.

So, I thought what I wanted was a table for the 'record' (single column primary key and some metadata), and a table for child fields linked to the record table through the record's primary key:

RECORD_TABLE:
    recordId,
    createdBy
    recordType

FIELD_TABLE
    fieldId,
    recordId,
    fieldName,
    fieldValue

Adding and updating rows hasn't been a problem. The problem comes when I want to retrieve a list of records, say to display the top 10 issues on a web page. What I want (for various reasons, one of which is databinding in ASP.NET) is one row returned per record, with each field value in a column where the column name is the field name. So I would get a rowset like:

recordId    createdBy    recordType    Author    IsClosed   IssueTitle
1           geoff        Issue         Geoff     No          'Some issue'
2           geoff        Issue         Geoff     No          'Another issue'

So, basically I think what I want is a stored procedure that will (somehow) munge the data for selected records into one row per record.

Is this even possible? If it is, is it a good idea? Or is there a better way to achieve the same effect (a better DB structure or some such)?"

<edit> to fix display </edit>

Edited by - robvolk on 05/21/2003 07:43:41

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-21 : 07:49:03

What you're describing is known as an attribute-value structure (or something like that). While it's flexible in structure, it's usually less than ideal, mostly for the reasons you've indicated. If you absolutely HAVE TO use this structure, and you need to present results like your example, you can use dynamic cross-tabbing to do it:

http://www.sqlteam.com/item.asp?ItemID=2955

However, I think you'd be better off creating real tables with the real structure you need to store the data properly. The whole idea of a relational database is that you define structures to hold data, and constrain it to that structure. When the structure is too flexible you invariably end up with either logical or data integrity problems, or both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources