Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Note: I am a developer...

I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign key constraint need to look like this...

ALTER TABLE "dbo"."Assets" 
ADD CONSTRAINT "Locations" FOREIGN KEY ( "LN", "LocationCode" ASC ) 
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

ALTER TABLE "dbo"."Assets" 
ADD CONSTRAINT "CostCentre" FOREIGN KEY ( "CC", "CostCentreCode" ASC ) 
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

So there will be a relationship to the outlines table via a fixed value of "LN" and "CC". Or will it be easier to change the database to have two table and remove the Type column on the OutlineFiles table? I am attempting this in Sybase BUT we support many databases.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:

  • can't have filtered foriegn keys
  • can't FK to constants
  • can't have multiple parents

Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I understand

CREATE TABLE OutlineFilesCostCentre (
    CostCentreCode ...NOT NULL --PK
    ...
    )

CREATE TABLE OutlineFilesLocations (
    LocationCode ... NOT NULL --PK
    ...
    )

CREATE TABLE Assets (
    ...
    CostCentreCode ... NOT NULL,
    LocationCode ... NOT NULL,
    ...

    CONSTRAINT FK_Assets_CostCentre" FOREIGN KEY ("CostCentreCode")
                REFERENCES "dbo"."OutlineFilesCostCentre" ("CostCentreCode"),
    CONSTRAINT FK_Assets_Locations" FOREIGN KEY ("LocationCode")
                REFERENCES "dbo"."OutlineFilesLocations" ("LocationCode")
   ...)

If you have 30 codes to lookup you will have 30 lookup tables: this is correct.

If you insist on OTLT you'll have to add extra columns to store type in Assets and FK them to your OTLT. I wouldn't do this.

    ...
    CostCentreType char(2) NOT NULL,
    CostCentreCode ... NOT NULL,
    LocationType  char(2) NOT NULL,
    LocationCode ... NOT NULL,
    ...

Or use triggers to maintain the correct codes. I wouldn't do this either.

share|improve this answer
    
Sorry made a mistake with the Alter Table statement. Fixed it up. However converting to multiple tables seems to be a better idea as I need the foreign key constraints for MS Entity Framework to code generate the mapping for me. –  David Gray Wright Aug 26 '11 at 6:44
1  
@David Gray Wright: I've changed my answer to match your columns. Multiple tables is the correct way. OTLT is a bad idea. –  gbn Aug 26 '11 at 8:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.