Post reply

Table Design issue

  • April 1, 2009 at 9:26 am

    #130374

    We have table with 250 columns having different data types. Now, some more columns need to add for the new requirement. and some more columns may required to add in futrure.

    Which one will be more efficient structure for the table

    1. Adding more columns in the same table, which may not be filled all the columns for a records many of them can be NULL.

    And it may fail updation and insertion if all the columns are filled because of row size constraint.

    2. A table with one row per field that has a unique key, a record ID, a field ID, and a field value. If a record has 250 fields filled in, the table would have 250 rows, each with one field value.

    And field values will be different in data types (varchar,int,float, etc..) therefore different columns may required to hold the filed values. Because one column cannot hold all kind of values.

    And this structure required more joins to get the result.

    Please advise better table design solution!!! Any suggestions & comments will be appreciated

    Thanks

    Manoj Kumar

  • April 1, 2009 at 9:41 am

    #970003

    Is that table properly normalised? Do all of those columns belong in there?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 1, 2009 at 9:47 am

    #970008

    say you want to add 100 new fields to your Table250.

    I would simply add a new table, with a foreign key to the original table, and a UNIQUE constraint on the FK column to assure a 1:1 relationship to the master table; each of your 100 "new" columns go into this related table instead.

    then you modify the update so that it updates/inserts into the child table as well.

    As you already identified NEVER try to use a single column for different values...you'll regret it down the road.

    example:

    CREATE TABLE MASTERTBL (MASTERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    COL1 INT,

    ....

    COL250 VARCHAR(30) )

    CREATE TABLE CHILDTBL (CHILDID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    MASTERID INT REFERENCES MASTERTBL(MASTERID),

    COL251 INT,

    ...

    COL350 DATETIME,

    CONSTRAINT UQ_CHILDTBL_MASTER UNIQUE (MASTERID) )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 1, 2009 at 9:49 am

    #970015

    GilaMonster (4/1/2009)

    Is that table properly normalised? Do all of those columns belong in there?

    Gail has identified the root cause of your problem...I've yet to see a table with that many columns that was normalized...a lot of that data should be yanked out into related tables, and the original columns dropped...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 1, 2009 at 9:55 am

    #970025

    I'm with Lowell, you really want to look at this. Granted, it might cause some development issues, but it might make things work better down the road. Be sure things are normalized well.

    If you can't change it, I'd go with the 1:1, vertical partitioned table Lowell mentioned.

  • April 1, 2009 at 10:19 am

    #970036

    Thanks for your prompt reply....I think, i can not change the structure of the extisting table because big application is running on that sturcture.

    I apriciate your valuatble suggestions

  • April 1, 2009 at 10:23 am

    #970048

    manoj kumar (4/1/2009)

    I think, i can not change the structure of the extisting table because big application is running on that sturcture.

    Change and rename the tables and then stick a view there that the app will use. Put an instead-of trigger on it and the application shouldn't notice the difference.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply