Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Cairo
    Posts
    1

    Unanswered: Dynamic fields in SQL

    I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
    using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
    any suggessions???
    Thnak

  2. #2
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    2

    Re: Dynamic fields in SQL

    The standard solution for your problem is to model the problem in the database, stroring the user defined attributes in a separate table.

    Something like:
    create table MY_TABLE (id int, name varchar(32) primary key(id))
    create table MY_DYNAMIC_ATTRIBUTES
    ( id int
    , attribute_name varchar(20)
    , attribute_type_code char(1)
    , attribute_value varchar(255)
    primary key (id, attribute_name)
    foreign key (id) references MY_TABLE(id)
    )

    The problem is usually the application reading and writing these tables.
    A general report would need crosstabbing.

    Specific reports, where you already know which fields are involved is easier, could even be done with a view:

    create view MY_DYNAMIC_VIEW as
    select
    t.id
    , t.name
    , d1.attribute_value as address
    , d2.attribute_value as city
    , convert(int, d3.attribute_value) as age
    from MY_TABLE t
    left join MY_DYNAMIC_ATTRIBUTES d1 on d1.id = t.id and d1.attribute_name = 'address'
    left join MY_DYNAMIC_ATTRIBUTES d2 on d2.id = t.id and d2.attribute_name = 'city'
    left join MY_DYNAMIC_ATTRIBUTES d3 on d3.id = t.id and d3.attribute_name = 'age'

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Dynamic fields in SQL

    Originally posted by plextoR
    I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
    using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
    any suggessions???
    Thnak
    Suggestions?

    Yeah, don't do it...

    Just think what kind of mess you'll end up with...


    Ummmm I want a varchar(8000) column...ummm I want another one...and another one...


    Booooooooooooooooooooooom

    What's business requirement to support, in non tech terms...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •