December 3, 2008 at 8:38 am
We collect personal registration data for events (conferences etc). To make things more manageable, we now have one database that manages multiple event projects. But while each project has a lot of fields in common (firstname, lastname, companyname, contact details etc) they also have their individual quirks (one might ask for golf handicap, another might ask you to select from a list of restaurants to eat in, for example).
Currently we have a main table that has about 40 fields, and we'll store the project-specific data in one of those fields that's not being used already for that project (e.g. we might store golf handicap in the 'fax' field).
This isn't ideal - if you're working on the db and don't know fax=golf handicap then you're in trouble.
But I don't want to add a load of fields that are only used for some projects and never used again, and I do want to keep things as simple as possible for the administrators. We have people who produce SSRS reports based on this data, but who don't know much about SP's, TSQL, XML etc.
I'd be very grateful for opinions on the way to tackle this issue! Thanks.
Nick
December 3, 2008 at 8:58 am
In the past I have used EAV tables for something like this. Basically you set up an attributes table (id, name, owner_id, applies_to_id) where the owner is the id of the project or person that owns the attribute and the applies_to_id would be the entity you want the attribute to a apply (project_id, person_id, etc...). So if project A has an attribute "Handicap" that applies to a person then when a person is created in that project you would apply that attribute to the person in a person_attributes table (person_id, attribute_id, attribute_value). This assumes that if the same person is involved in multiple projects there would be a new person entered.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 6:59 am
Many thanks for the response Jack.
I've used EAV tables before (although I didn't know that was what they were called!). I think they do the job when it comes to pulling data out, as I can probably use a stored proc to transform them into a standard table layout if I really need to.
The issue I have with them is maintaining them - particularly writing to them - as you have to check for existing values, validate differently, etc. Plus you end up doing one thing for the fixed fields, and another for the EAV pseudo-fields... it wears me out just thinking about it!
I was wondering if there was any way to 'alias' field names. For example, I'd be happy to use the 'MiscData1' field to store golf handicap for projectID 1, and store restaurant selection in the same field for project 2, if only people didn't have to remember what they mean for different projects. I have a maximum requirement for 20 of these undefined fields per project.
It leads me to wonder if there's any low maintenance way to 'alias' fields.
e.g.
ProjectID,Table,Field,Alias
1,tblPeople,MiscData1,Golf Handicap
2,tblPeople,MiscData1,Restaurant
Ideally there would be a 'translation layer', so that I could run queries like:
Select firstname,lastname,[golf handicap]
from [something]
where projectID=1
... where [something] would look up the alias and know that what I actually wanted was...
Select firstname, lastname, miscdata1 as [golfhandicap]
from tblPeople
where projectID=1
I'm just thinking out loud about my ideal scenario here to see if I can trigger some thoughts!
Nick
December 5, 2008 at 11:21 am
Just brainstorming...
Using views to simply alter fieldnames costs very little. Ever think about having a separate view for each project? I've never done this in practice, but for what you are describing, it seems you could create a table with some number of varchar columns and then just use the view (or CTE) for a particular project to change the field names. You're sacrificing the ability to impose meaningful constraints on these columns at the db level, but you could still build indexes if needed. It doesn't sound as if this is the kind of data that would require either.
I've seen XML columns used to store data from a flexible UI that allowed users to design their own forms for entry of items that were not standard database columns. The good thing was that the XML was almost infinitely flexible. The problem with that approach was that it was totally nonstandard. Some UI-created fields really should have been added as standard columns and indexed. Some users chose to change the prompt/field name, which resulted in the tag names being different in the final XML. I do not advise this.
Either way, be careful, but goodluck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply