The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Table Setup

Hi,

Can anyone recommend the best way to setup my sql table for the following scenario:

I have a table of items:

ItemID  Name    Desc
1        Villa  Lovely Beachy Villa

I need to allow users to add their own custom attributes for items, for example, in another table:

customID  ItemID  UserID    Description
1        1        100      Bedrooms

I then need to store the values for these custom attributes, for example, in a third table:

customID  Value
1        5

I may well have answered my own question but thought that it was worth asking some experts!

Thanks
Kieran
Kieran
Wednesday, June 21, 2006
 
 
If you want to associate attribute values (not attribute types) with items, then ItemID should be a field of the 3rd table not the 2nd.

What's the type of the user-definable attribute Value field: integer, string, date, blob, ...?

When a user defines a new attribute type, does she define a finite list of its associated values? If so then instead of having ItemId in the 3rd table, then perhaps you want a ValueId field, and a 4th table which contains ValueId/ItemId pairs.

How do you intend to use the UserId field in the 2nd table?
Christopher Wells Send private email
Wednesday, June 21, 2006
 
 
@Kieran,

Is your intention to reuse the custom attributes across multiple items? So a user could add "Bedrooms", for instance, and then describe the number of bedrooms for many items?

Or should an attribute like "Bedrooms" apply only to one item?

The answer to that question will dictate what schema you should use.
PWills Send private email
Wednesday, June 21, 2006
 
 
Thanks!

There are many user of the same system, each with their own items for their business. The idea is to let them add custom attributes, so that they can effectively customise the information held against their items, giving flexibility to the user. So one user may store 'bedrooms,pool,view' and another may store 'kitchen,location,floors', I'm trying to ensure that the system can cater for users items no matter what their attributes.

Does this make sense?

Thanks again
Kieran
Kieran
Wednesday, June 21, 2006
 
 
==> Does this make sense?

NO!

What you are describing is commonly called "EAV Entity Attribute Value" -- google those terms. My business partner jokinly calls this "the database within a database trap"

Without getting into any long, drawn out discussion about it (you'll find those from google), let me just say this: You're setting yourself up for a nightmarish database. If you can avoid it at all, you do not want to go down that path.

I despise them so much because ... well, lets just say I've "been there, done that" and really don't like where it ended up.

'nuff said.
Sgt.Sausage
Wednesday, June 21, 2006
 
 
I don't think this is EAV, this is normalized tagging.

The question which remains for me - is there one universe of attribute values or does each user (or a subset of users) have their own list?
Cade Roux Send private email
Wednesday, June 21, 2006
 
 
Re-reading this, it sounds like you also want to store a number in some cases?  For something like bedrooms, this should be a value column, pure and simple, and not a customized attribute.  I can see assigning user-defined attributes or tags the way you've got it though.
Cade Roux Send private email
Wednesday, June 21, 2006
 
 
It sounds like the OP wants the users to be able to store different things. Flexibility.

The thing is, the OP has not completely defined it. The OP wants the users to be able to store different things BUT THE OP CAN PREDEFINE THOSE THINGS THAT NEED TO BE STORED.

Any type of dynamic attribute stuff is insane.

It is an incorrect db design.

It will only bring you pain and suffering.

Anyone that has had to clean up stuff like that will understand, the rest won't.
D in PHX Send private email
Wednesday, June 21, 2006
 
 
> ... "EAV Entity Attribute Value" -- google those terms.

I just have: http://www.google.ca/search?q=EAV+ENTITY+ATTRIBUTE+VALUE

Apparently it's used a lot in clinical databases, for example, in which patients have an unlimited number of attributes.
Christopher Wells Send private email
Wednesday, June 21, 2006
 
 
Cade Roux,

Each client would have their own set of attributes.

Thanks
Kieran
Kieran
Thursday, June 22, 2006
 
 
> EAV is used a lot in clinical databases

I suspect it's used for storing schemas too, i.e. the database's metadata; and, used when an XML schema and/or document is mapped to relational data.
 

> 'bedrooms,pool,view' and 'kitchen,location,floors'

Some of these attributes are numbers and some are strings.

http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm suggests you'll therefore have more than one AttributeValues table - one table which contains for numeric attribute values, another for string values, etc.

Part of your problem may be building a UI which lets the user query on their attributes: for example if I can create a 'number of bedrooms' attribute than I'll want to be able to select items WHERE (number of bedrooms > 3).

A quick Google found http://sice527.ddns.umkc.edu/pedlab/Secure/Web_Metadata_UI.pdf talking about UI implementation issues.

If on the other hand you don't need to create custom queries on user-defined attributes then perhaps you wouldn't need user-defined attributes at al: instead you might have a simple, single, free-text 'Description' column in which a user enters anything she wants.
Christopher Wells Send private email
Thursday, June 22, 2006
 
 
I'm getting a little confused here:

customID  ItemID  UserID    Description
1        1        100      Bedrooms

Because this seems to imply that the user will assign a Bedrooms value to one item but not others, and custom is just holding the value.  I'm not sure why custom was normalized out, yet the Description wasn't normalized out into an attributes table like:

AttributeID, UserID, Description

I think if you are having arbitrary data attached by a user to an item, just put a single XML column attached by user to item:

Item ID, UserID, XML

And you're done.

But I think this is bound to end in tears.  A lot of the attributes you have mentioned should simply be columns to facilitate proper queries and a decent design.  If the user doesn't populate them, you can handle that in the UI.
Cade Roux Send private email
Thursday, June 22, 2006
 
 
The system will be used by many clients to display their products.

The core of the item details can be standardised for all clients but

Client One may be dealing in properties and would want several attribute
relating to properties.
So all items would have custom attributes like Bedrooms, Garage etc.


Client Two may be dealing in cars and therefore would want attributes
relating to cars.
So all items would have custom attributes like No of doors, automatic
etc.
 
So we are looking for the system to hold the custom attributes in one
table.

Client One                Client Two
    Item                    Item
                    
        Villa                    BMW
        ?200k                    ?50k

    Custom                    Custom
        Bedrooms 3                Doors      2
        Garage  Yes                Automatic No    


We would know from the client the type of business (property or car).
We would also need to be able to search on the custom attributes.

Thanks
Kieraan
Kieran Southern
Friday, June 23, 2006
 
 
In order to avoid the database within a database nightmare, it sounds like XML and XQuery in SQL Server 2005 would probably serve your needs best.

Yes, it's not relational and yes, it's not SQL.
Cade Roux Send private email
Friday, June 23, 2006
 
 
Why is XML better than SQL for this purpose?

What's the problem with using SQL in this way, and why doesn't using XML have exactly the same problem?
Christopher Wells Send private email
Friday, June 23, 2006
 
 
If a user wants to select based on their custom attributes, e.g. select all houses where bedrooms > 3, this can be done if the implementation uses an SQL schema like the one described above ...

select * from ItemsTable join IntegerAttributeValueTable where (ItemsTable.ItemId = IntegerAttributeValueTable.ItemId) and (IntegerAttributeValueTable.AttributeTypeId = %attribute_type_bedrooms%) and (IntegerAttributeValueTable.IntegerValue > 3)

... but it can't be done on the SQL server if the custom attributes are stored in attached SQL documents.
Christopher Wells Send private email
Friday, June 23, 2006
 
 
> attached SQL documents.

attached XML documents.
Christopher Wells Send private email
Friday, June 23, 2006
 
 
SQL Server 2005 supports XML stored in new XML-type columns and the XQuery (not attached)

It's not relational, but can be used to solve these difficult hybrid relational problems without resorting to EAV/database-within-a-database problems.  I'd still want some kind of schema design encompassing the various businesses who are storing their arbitrary data.

Personally, I'd avoid both the above as a bad design destined for long-term failure (you can't please all of the people all of the time), but that didn't appear to be an option here.
Cade Roux Send private email
Friday, June 23, 2006
 
 
In practice, the time and effort required to customize the user interface for each client, will largely negate any benefit from having a single free form database.

The closest and perhaps best analogy is the sales register, but it should be emphasized that from the database's perceptive, each item is simply that - it makes no distinction between houses and cars, or whether cars should have two doors and houses, three bedrooms. The primary disadvantage of this approach, as you've probably noticed, is that it's extremely difficult to find all entries that consist of three bedroom houses, but not apartments.

I've found in the long run, it's actually easiest to develop a user interface framework with a common set of controls, a skeleton database with some universal tables (such as billing info), and then customize for each niche.  Err...  instead of having one universal product, give yourself the ability to create many different products in mere days.
TheDavid
Monday, June 26, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz