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.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2010-04-07 : 03:20:19
|
| Hi all,I have been thinking about an idea for a little while that I wanted to discuss with you. I know it's kinda "far out" but I wanna discuss it anyway. The thing is that I'm currently designing an EAV-modeled store for PersonData where a Person has a few fixed properties like Name, DOB, Nationality and then each customer that purchases our software can create their own PersonTypes (Customer, Employee, SupplierEmployee, etc) with PersonTypeProperties (InvoiceAddress, DateOfEmployment, etc). Pretty straight forward EAV-stuff. Now as most of us know, with an increasing amount of Persons and PersonTypes registered the design will cause decreasing performance and this can become a problem. However, after an initial period, say a month or so, the PersonTypes and PersonTypeProperties are more or less static. Can the fact that these definitions rarely change be utilized?? Would it be possible to create physical tables dynamically out of the PersonTypes, populate the physical table with a dynamic pivot statement on a schedule or something? And whenever you need to do a search or something, actually query the physical table instead of the EAV-modeled PersonTypePropertyValues? There are some major issues here of course, but I'm trying to figure out a way to utilize the flexibility of EAV and make use of effective queries and proper datatypes. If I could make this work, it would mean that I could for example query for all employees that were hired within the last 6 months in an effective manner which would be extremely valuable. What do you think? Is it possible to make something like this effectively? How does Sharepoint and all those other CMS-systems do this...?- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-04-07 : 04:20:55
|
You can try using the new SPARSE column(set) available in SQL Server 2008 and later.Or, you can try to use XML as "datastorage" for the persondataproperties.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
|
|
|
|
|
EAV with a twist...?