| Author |
Topic |
|
WeeBubba
Starting Member
18 Posts |
 Posted - 2008-08-17 : 21:25:50
|
| hi there i have a database architecture conundrum that i am finding it difficult to solve. i will be most grateful for any suggestions. i must be honest here and say that this problem relates to a firebird database. but from previous posts re SQL server, i know that this forum is frequented by some top SQL brains. so i am hoping you will still be kind enough to assist me. i think that this is really an SQL/table structure issue anyway so it does not really matter what db it is to some extent (i think). i hope this is ok. in my database i am storing user defined fields info per client. i have chosen to do this by creating rows of data rather than fields. my db structure is like this:CLIENT------IDNAME (e.g. 'Bob')UDF_DEFINITION--------------IDDATATYPE (e.g. 'Text' or 'Date')DESCRIPTION (e.g. any client created field e.g. 'Sales Value')CLIENT_UDF_VALUES-----------------IDID_UDF_DEFINITIONID_CLIENTVALUE (i.e. a VARCHAR value depending upon datatype)the problem here is that i am now asked to build a search facility to retrieve client data which must have an ordering capacity.my problems are 2 fold:1) the smaller problem is that in CLIENT_UDF_VALUES the value is always held in VARCHAR format (i cast back in code). so i cannot properly do a comparison where somebody wants to search e.g. by UDF_DATE > '01/01/2008'. or where UDF_CURRENCY > $10.2) the main problem is that in order to sort AND PAGE the data i must first run the query joining the baked client data to the udf data. then sort afterwards. then i can page the data and return a specific rowset after this. there is no cross tab query ability in firebird. and cross tab is slow anyhow. and this is necessitated by the fact my UDF values are held in rows rather than columns.I have considered changing this architecture to dynamically add or remove columns for UDF values in a table but this seems unstable and somewhat non standard practice to me. i have also thought about duplicating the search data in a a flat file format table via triggers. but i am still presented with problems for datatypes here etc.any assistance is very appreciated. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-17 : 23:20:56
|
| Welcome to the nasty world of EAV.Search this forum for EAV - it's been done to death and no need to start another thread.My 10 second summary: have x number of spare columns of various types for your UDF data. Use the meta-data for identifying & labeling the columns but always form a proper query against a static table structure. If you really must have other stuff then store it as you have it but disallow searches on it, for the reasons your are seeing now. |
 |
|
|
WeeBubba
Starting Member
18 Posts |
Posted - 2008-08-17 : 23:47:02
|
| Hi loztinspacethanks for the reply. i hadn't heard of the term EAV before, so apologies if this is a well trodden path i am limping down. the EAV structure we have in place worked OK up to now because we always loaded the data 'en masse'. we are only now looking at scalability questions such as paging and searching. i guess this should have been thought of before. but we are a small development team and you know how it goes. "I want it all! And yesterday!" To be fair, the EAV architecture was working OK up until now. The only problem I have with your suggestion is what if a client sets up 400 new user defined fields? (we have 150+ so it is possible). Will performance suffer because of many fields in a table?what are your feelings on altering a table on the fly? i.e. adding new columns to the flattened table as and when a user inserts a new user defined field?finally, are you suggesting putting all the defined columns in one table e.g. having 100 date columns, 100 text columns, 100 money columns etc? not splitting across tables per data type?many thanks |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-18 : 00:41:54
|
| It may well have worked in the past, but that was then and now is now. You now have different requirements and you will hit the limitations of this design (one of many).Alter on the fly - no way I would consider this. Generally a database schema should be static between releases.Your final sentence basically describes what I was advocating, but I would not have envisaged it on such a scale - more of 5 spare ints, 5 spare dates etc on a singe table, but you're close enough. Depending on how stuck you are, can you have one value column per type? Not something I've done, but a suggestion that might get you out of a hole (or not!).Unfortunately there is no good way of doing EAV db intependently and I can't really help with Firebird. Maybe there is some kind of variant type you can use instead of varchar? |
|
|
|
WeeBubba
Starting Member
18 Posts |
Posted - 2008-08-18 : 01:00:48
|
| hi and thanks again for your help.having one value column per type is something i have considered. this will help me solve problem 1. i.e. i will be able to comparison searches e.g. where date > x.however, this does not solve the paging issue as i still have pivot data. so the work involved to do that change is not worth the benefit IMO.i am trying to operate an agile environment here so i have no qualms about doing some major surgery to our defined fields, if that is what is necessary.at the moment in our test data we are running 150+ user defined fields. so having a table with 400+ columns seems to me to be 'dodgy' (i could be wrong). seems like i am stuck between a rock and a hard place!we have to provide a solution for a client by monday. :) |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-18 : 04:27:28
|
Like I said, I don't know about Firebird, but I'd be reaching for the XML type by now if it was SQL Server.quote:
Originally posted by WeeBubba
we have to provide a solution for a client by monday. :)
I'd say you're fucked then. |
|
|
|
WeeBubba
Starting Member
18 Posts |
Posted - 2008-08-18 : 07:05:52
|
| ha :) |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-19 : 10:22:02
|
quote:
Originally posted by WeeBubba
To be fair, the EAV architecture was working OK up until now.
To be fair, EAV always looks good on paper. It is the Communism of the database world. It is when you try to scale it up in size or features that its flaws become apparent.My 10 second summary: store your required/permanent fields in relational form. Store your optional fields in an XML column.Boycott Beijing Olympics 2008 |
|
|
|
WeeBubba
Starting Member
18 Posts |
Posted - 2008-08-19 : 20:26:21
|
| Hi BlindmanThanks for your reply.Do you have any URLs or resources where I can study how to effectively create a part relational/part XML architecture that would facilitate better performance. I am keen to explore this avenue as all others seem dead right now. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-19 : 20:59:12
|
| http://www.w3schools.com/xml/default.aspBoycott Beijing Olympics 2008 |
|
|
|
WeeBubba
Starting Member
18 Posts |
Posted - 2008-08-20 : 03:14:17
|
| hi blindmani have a fairly good understanding of XML. i was thinking more about resources which show how XML can be used within a database instead of an EAV architecture.if you know of anywhere i could get more info on this then i would be very gratefulthanks |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-20 : 10:09:55
|
| That would be specific to your database, which is Firebird, and this is a SQL Server forum.Boycott Beijing Olympics 2008 |
|
|
|
WeeBubba
Starting Member
18 Posts |
Posted - 2008-08-20 : 17:01:25
|
| hi againhave u got any resources for sql server for this? i can then apply this to firebird afterwards. if not, dont worry, ill hunt aroundta |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-20 : 19:25:08
|
| SQL Server 2005 has an XML datatype.SQL Server 2000 does not. You could always store the XML as a string, but then searching against it would be very difficult.Boycott Beijing Olympics 2008 |
|
|
|
|
user defined fields database architecture