Post reply

Horizontal Partition of states with identity?

  • November 20, 2004 at 11:29 pm

    #63564

    Hello,

    I am building an application that will be used in all US states.

    I have the tables divided like this, (Listings_CA, Listings_NY, Listings_FL... etc.)

    column schema for above table Listings_CA (only difference

    between tables is constraint)

    "Id int IDENTITY(1,1)," +

    "StateId INT NOT NULL PRIMARY KEY " +

    "CONSTRAINT ListStateId_CHK1" CHECK (StateId = 1)," +

    "UserId int NOT NULL," +

    As you can see i am trying to design the app so that the tables are horizontally partitioned on the stateId (Ex. where CA is a stateId of 1).

    I am using a view called Listings to bring them together using UNION ALL.

    I want to be able to do an INSERT using the VIEW 'Listings' but it is failing because I have an Identity column in my table.

    My question is... how can i get around this and still have an Id column for my Listings?

    Thanks guys!

  • November 23, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • December 6, 2004 at 5:07 am

    #532611

    I think you have to read more about

    (Instead of Insert) Triger

    and put it on the view

    so when you insert your data ... you check for State and the insert data in the Appropriate Table

    does this help u?


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • December 6, 2004 at 6:56 am

    #532627

    Vertical partioning, this is, hm?  Horizontal = moving attributes (i.e., columns) to a new table, not moving rows.  edit: Yoda a little tired from the weekend!

    Why do you feel you must do this?  Partioning can be done after the fact, should performance prove to suffer. 

    Should you feel that parition you still should do, your UNION view , composite virtual primary key of State/Identity it shall have.

    Optimize only when needed for performance!  To over-engineer, a quick path to the dark side!

     

  • December 6, 2004 at 7:15 am

    #532630

    what about freeing yourself from the identity column

    and use a newe table that has the last ID number .. and when you want to insert .. get Last number + 1 and put it on the table you want (from the instead of View)

    For Yoda, Vertical = moving attributes (i.e., columns) to a new table, not moving rows.  

    and I think Horzintal = Move Data .. and this problem talk about this .. am i right?


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • December 6, 2004 at 7:36 am

    #532639

    oops.. yes, Yoda had it backwards. Long weekend, indeed!

    Either way, unless a convincing reason is given, done this should not be!

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

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