By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

Dynamic Columns in a table

P: 1
I have a table called ticket that has tickets stored in it. I have a seperate table called line items with the line items in it that are tied back to the ticket via a foreignkey. Not all line items need to have the same columns as some products vary greatly in data attributes. How do I set up my tables to allow for Dynamic Columns based on the line item type. I have tried creating another table that holds attribute names and values based on there lineitem type but when my datatype changes it messes everything up.

create table ticker(ticket_id, ..., ..., ...)
create table lineitem(item_id, ticket_id, type_id, ..., ...., ...)
create table type(type_id, ... , ....)
create table attributeNames(attribute_id, type_id, ...)
create table attributeValues(attribute_id, item_id, attribute_value)

Line item is subordinate to ticket and has a type based on the types in the type table. based on what type a line item has the line item can have several attributes(we get these from attribute name table that is subordinate to type table) based on the attribute and the item id in the attribute value table you can tell which attribute values are assigned to your line item with the attribute name. I thought this was pretty nifty but now when I need to change the data type Im stuck. Any ideas....... if Im setting this up completely wrong please let me know thanks in advance..

Feb 26 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,134
If I understand you correctly you will need to use dynamic sql for that

here is a link to a question solved by using a dynamic query
Feb 26 '08 #2

Expert 2.5K+
P: 2,878
How about creating a product_id and a product table instead?

-- CK
Feb 27 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.