SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 My database design...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 04/06/2008 :  08:23:57  Show Profile  Reply with Quote
I've got a table of computers and each computer is assigned to a computer group.

I also have a table of content and a content item is assigned to multiple computer groups.

This gives the following structure:

Computer
---------
ComputerID int pk
GroupID int fk

ComputerGroup
---------------
GroupID pk

ComputerGroupContent
--------------------
GroupID fk
ContentID fk

Content
-------
ContentID pk


Now, this is the bit I'm not so sure about....

Each content item can have a number of attributes that can be customized by a computer. So for example, a piece of content has an attribue called 'Title' that each computer can provide a value for it

Thus here are the additional tables:

ContentAttribute
----------------
AttributeID int pk
ContentID int fk
Name string

AttributeValues
---------------
ComputerID int fk
AttributeID fk
Value string


So the issue I have here is that there's nothing to stop a computer from having content attribute values for a content row that is not actually assigned to the computer's group!

Does this suggest a poor database design?

Cheers, XF.

Edited by - X-Factor on 04/06/2008 08:27:26

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/06/2008 :  22:57:34  Show Profile  Visit jezemine's Homepage  Reply with Quote
main problem I see is that you are using the EAV model, which has terrible performance. It leads to very complicated code for fairly simple queries.

see: http://weblogs.sqlteam.com/davidm/articles/12117.aspx


elsasoft.org
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/07/2008 :  04:15:09  Show Profile  Reply with Quote
More exciting reading:
http://www.dbforums.com/showpost.php?p=6328567&postcount=27
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 04/07/2008 :  05:42:03  Show Profile  Reply with Quote
Hey thanks for your replies.

I read the article...

1. The author doesn't give an alternative to EVA. He just tightens it up with a look-up table to hold the type of the attribute. I'm not sure that I even need this because my attributes (at the moment at least) really are all strings.

2. I would love to implement a compact or exploded design but the client really does need to be able to add new content with variable sets of attributes without getting someone in to change the database schema.

I suppose the alternative would be to de-normalize the attributes and have, say, twenty columns in the content table. That should be enough space... nah!

3. The problem of multiple joins to do queries is not really an issue with this app.

Firstly, before the data is consumed, it is output to an xml file. The xml is then uploaded to a seperate system. So performance isn't such an issue and its actually easier to have the attributes as a set of rows becuase I can just iterate over them when writing them out to xml instead of having to have special case code for each type of content to determine which columns need to be output.

Secondly, there's no real need to search for content based on an attribute value. Virtually all attributes are specific to a single piece of content anyway.

Most of the queries will be something like... select * content where contentid = 12 and select * attributes where contentid = 12

I think the only draw back is the content entity being spread over multiple rows and the extra code this entails. This isn't such an issue because LLBLGen OR mapper hides all the SQL.

Having said all that, I think this would be the perfect opportunity to use the xml data type. There's nothing in relational theory that prohibits using another relation as a column. I'm not quite sure how this would work but it doesn't matter anyway because I'm using MySQL today.

Another thing, doesn't the exploded design (which I think is more appropriate to my situation than compact) have the same issue with table joins as EVA?

Any more feedback?

XF.


Edited by - X-Factor on 04/07/2008 06:11:14
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/07/2008 :  08:16:44  Show Profile  Visit spirit1's Homepage  Reply with Quote
maybe you should look into EAV with xml?
http://weblogs.sqlteam.com/mladenp/archive/2006/10/14/14032.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/07/2008 :  12:10:51  Show Profile  Visit jezemine's Homepage  Reply with Quote
if you never need to search based on the EAV values, then EAV can be ok. the perf problem comes when you need to write queries that query on an EAV value - then you have a join for each value, rather than another predicate in a WHERE clause.

that is, this will be ok:

"get me the computer with ID=12"

and this will have bad perf (would require 3 joins on the AttributeValues table)

"get me all computers where computername='asdf' and purchasedate > '20080101' and model='Dell'"




elsasoft.org
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 04/07/2008 :  12:36:06  Show Profile  Reply with Quote
Thing is, if one doesn't have access to an xml data type, what alternative is there to EVA?
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/07/2008 :  13:55:48  Show Profile  Visit jezemine's Homepage  Reply with Quote
if the requirement is to be able to add arbitrarily many new attributes without changing the schema, I don't think there is an alternative. maybe someone else knows of one.


elsasoft.org
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/08/2008 :  03:16:47  Show Profile  Reply with Quote
Give all your users db_owner access? You either change the schema literally or use EAV (not EVA btw ) to store the schema as data and change that data.

I would also extend Jesse's comment - the other "minor niggle" (other than performance) with EAV is relational integrity. Without a lot of custom code and ensuring that all changes are performed via your code you will one day kiss goodbye to your data. We had a maverick user virtually decimate an EAV db because the vendor's code did not limit what they were able to do.

Long and short of it - the blessing of EAV is the flexibility it gives you. The curse of EAV is the flexibility it gives you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000