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 |
|
Laughing_John
Starting Member
3 Posts |
 Posted - 2005-06-30 : 17:36:33
|
| Help !The programming problem:1) I need to be able to create a description of a thing (lets call it an item) and save the list of fields that make up that item. The fields could be of any one of a subset of data types.2) I could then define another item with a whole different set of fields.3) I then need to create the physical data for the two 'item types' I've just created.4) Later on I will need to select/report on the items.I started thinking I could have 4 tables, one containing the type of items, one the type of fields, one the actual items and a fourth the data. Along the lines of:Item TypeItem_Type_IDItem_Type_NameItem_Type_DescriptionField DescriptionsItem_Type_IDField_Type_IDField_Type_NameField_Type_Type (some representation of the data types: 1=int, 2=bigint etc ...)ItemsItem_IDItem_Type_IDItem_DescriptionFieldsItem_IDField_Type_IDField_val_intField_val_bigintField_val_varcharfield_val_datetimeetc etc.As you can see I have a bit of a problem with the fields table. The problem I have is that the type of the fields are not known when I design the database, so I have to be able to represent all of the types. The only way I could think of doing this is to have all of the types I need on the fields table, or alternatively having one table of data for each type of field.Whichever way I do it, I run into problems: - I need the database to perform, so ideally the values of the fields will be indexed. - For reporting/coding purposes I need the users/other programmers to be able refer to the items in a more traditional relational way.So assuming the item I've defined is a car, I need to be able to say "SELECT max_speed FROM Cars WHERE Gears=4" (apologies for the rubbish example). However in my database, I would have the metadata for the fields, and the actual select statement would be more complicated:SELECT f1.field_val_int FROM fields f1, fields f2, field_types ft1, field_types ft2, item_types it, items i WHERE f1.Item_id = f2.Item_id AND f2.field_type_id = ft2_types.field_type_id AND ft2.field_name = "gears" AND f2.field_val_int = 4 AND f1.field_type_id = ft_types.field_type_id AND ft.field_type_name = "max_speed" AND i.item_id = f1.item_id AND i.item_type_id = it.Item_type_id AND it.item_type_Name="car".... OK I got bored trying to work that out, but you get the idea. It's a nightmare.I figured I may be able to do something clever with views and create a flat table over my design, but I'm not having much luck there. The problem being that I have to select a different column from the fields table depending on the data type and I have no idea how to do that !!!I'm sure someone must have done something similar before, any help gratefully received.Apologies for the long post.LJ |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-01 : 01:10:20
|
Are you writing a database in SQL then?  A table for CARS would obviously be better than a generic table, but I'm sure you know that and thus you are working aroudn a real-world prolem.Instead ofField_val_intField_val_bigintField_val_varcharfield_val_datetimeyou could use a SQL Variant datatype, which will store anything, with perhaps an additional column to say what type you consider it to be storing - that way to extract the data you don't have to use a bunch of complicated code / CASE statements to get the data from the appropriate datatype column.Kristen |
 |
|
|
Laughing_John
Starting Member
3 Posts |
Posted - 2005-07-01 : 05:12:20
|
| Kristen,Thanks very much for that. I think really what I want is a different type of data store, but I don't have the time, inclination or indeed the brains to write one .... maybe there's one out there somewhere ...My problem is that the 'items' are not fixed so I can't create a cars table. The user could decide to create different types of item (to use my silly analogy: "car", "bicycle", "horse" or even "toaster" etc) - and I don't know what they are up front.SQL Variant sounds like a good idea - do you have any idea whether this would work ok with reporting tools (e.g. crystal) and things like that ? Also can I index a SQL Variant ? I was a bit put off them based on some stuff I had read, but maybe I need to do some more research .... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-01 : 06:25:05
|
| You'll hit some rocks like that for sure. Maybe make a VIEW with the data types in individual columns, CASTing the SQL Varient column in the table?Kristen |
|
|
|
Laughing_John
Starting Member
3 Posts |
Posted - 2005-07-01 : 07:23:36
|
| Thanks, you're a star! I'll give it a bash ... |
|
|
|
|
|
|
|
|
Dynamic fields on an item