Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    6

    Unanswered: Here is a question I can't seem to find the answer to

    Hi all. I'm kind of stuck. I may be having a general conceptual error here.


    Suppose I have a table with two columns in the primary key, ID and VERSION.


    Suppose I have a child table that I want a relationship on ID only and not VERSION.

    Is there a way to do this besides creating a check constraint? (ie When I print and ERD it gives the lines, so the "non" db folks can "see" the relationships)

    not sure if this is possible or not but any clues you all can give would be most helpful.

    Thanks,

    bill
    s e g fa h l t @ l o n g b o y s . n e t

  2. #2
    Join Date
    Jul 2002
    Posts
    3
    The conceptual error you have made is mixing up the parent and child. The table with id and version should be the child, and then it can have a foreign key relationship with the parent.

    Code:
    CREATE TABLE parent (
    	id int NOT NULL ,
    	string char (10) NULL 
    )
    GO
    
    ALTER TABLE parent ADD CONSTRAINT
    	PK_parent PRIMARY KEY  CLUSTERED 
    	(id) 
    GO
    
    CREATE TABLE child (
    	id int NOT NULL ,
    	version int NOT NULL ,
    	string char (10) NULL 
    )
    GO
    
    ALTER TABLE child ADD CONSTRAINT
    	PK_child PRIMARY KEY  CLUSTERED 
    	(id, version) 
    GO
    
    ALTER TABLE child ADD CONSTRAINT
    	FK_child_parent FOREIGN KEY 
    	(id) REFERENCES parent (id)
    GO
    Hope this helps.

  3. #3
    Join Date
    Jan 2002
    Posts
    6
    Thanks for the feedback. My apologies for not being more clear on the subject. I opted for brevity instead of details.

    Here is my situation.
    I have a very large table which I want to split up into smaller tables. The tables [should] ideally have a 1-1 relationship between them all.

    I also need to implement a transaction tracking solution such that I can determine if any column of any of the tables was changed, when it was changed, and who changed it, and be able to rollback if necessary to any particular point in time.

    Finally, I also need to implement a modification approval system, such that any proposed changes must be captured, run through the red-tape corporate pipeline, then when approved, put into the table as actual data. While the changes are in the approval process, I need to be able to show the entire record with the proposed changes to one set of individual, and the entire record with only the approved changes/data to another set of people.

    There is non-trivial amount of tables I have to do this with.

    I came up with 2 feasible solutions.

    solution 1 is : Each table which I need to track will have a copy table(same structure). It will store the proposed changes and any change history.

    solution 2 is a bit more abstract. For each of my tables which I need to have transaction auditing and modification approval on, I will add two columns. a version column and a stage column. The version column will be used mainly for reporting and an easy way to see changelog history for any particular item ID. The stage column will be of "proposed", "review", "approved", "rejected", "obsolete". All the data will be contained in one table. so for each unique record(identified by ID) I will actually have multiple records with the same ID. But each successive record will have a higher version number. As new Version Numbers are added, the stage will be moved to "obsolete" when the changes are approved. then that record's stage will be set to "approved" and it will become the official record.

    I just came across this yesterday, so i'm still fleshing out my "solution". I like the idea of solution 2 as its compact and fairly easy to code for. However, the more I think about it, the more I feel I will break anykind of RI I can put and still use the DB to enforce it. I don't think I even need a primary key of (ID, Version), but you need a primary key, to have a foreign key. If I could get the relationship, without having a primary key(just have a clustered, non-unique index on ID) then that would be sufficient. However, I'm pretty new to Sql Server, so I don't know if there is a way to do that. Any Ideas?

    here is a visual of what my solution 2 would be like.

    I have a table
    create table table1 (
    id int not null,
    version int not null,
    stageId int not null,
    more columns,
    primary key (id, version)
    )

    I have a child table, which I need a RI check on ID only of the parent table. It too, will have a version column, but does not relate to the parent table. It will however, be part of the primary key of the child table.

    create table child1ToTable1 (
    id int not null,
    version int not null,
    stageId int not null,
    more columns,
    primary key(id, version)
    )

    create table child2ToTable1 (
    id int not null,
    version int not null,
    stageId int not null,
    more columns,
    primary key(id, version)
    )

    I need the relationship from table1 to child[12]ToTable1 to be a many to many on ID, but I don't care about the version.

    Clear as mud? I know. it kind of gives me a headache too.



    Thanks for you help.

    bill

Posting Permissions

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