| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2008-04-06 : 08:23:57
|
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 pkGroupID int fkComputerGroup---------------GroupID pkComputerGroupContent--------------------GroupID fkContentID fkContent-------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 itThus here are the additional tables:ContentAttribute----------------AttributeID int pkContentID int fkName stringAttributeValues---------------ComputerID int fkAttributeID fkValue 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. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
pootle_flump
1064 Posts |
Posted - 2008-04-07 : 04:15:09
|
| More exciting reading:http://www.dbforums.com/showpost.php?p=6328567&postcount=27 |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-04-07 : 05:42:03
|
| 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 = 12I 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. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-07 : 12:10:51
|
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 |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-04-07 : 12:36:06
|
| Thing is, if one doesn't have access to an xml data type, what alternative is there to EVA? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-07 : 13:55:48
|
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 |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-04-08 : 03:16:47
|
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. |
|
|
|
|
My database design...