Semantically Dependent Attributes In database design, it's not uncommon to define a column to hold values of different kinds of objects or attributes. Look at the following logical design. Search_Result ================ SEARCH_RESULT_ID ---------------- -- SEARCH_OBJECT_ID -- | OBJECT_TYPE | | ================ | | | | | Book | | Magazine ========= | | ========= ISBN ----------- ------ ISSN --------- --------- TITLE TITLE PUBLISHER PUBLISHER ========= ========= Diagram 1 In entity Search_Result, if OBJECT_TYPE = 'Book', SEARCH_OBJECT_ID is one of ISBN's in the Book; if OBJECT_TYPE = 'Magazine', SEARCH_OBJECT_ID is an ISSN in the Magazine. The attribute SEARCH_OBJECT_ID has a different meaning depending on the value of OBJECT_TYPE. For this reason, let's call it a semantically dependent attribute. This kind of entity relationship is also called arc relationship. The line connecting Book to Search_Result or the line connecting Magazine to Search_Result shown above can only exist in logical design and does not indicate a foreign key constraint in the physical design. Either the application will be responsible for making sure a SEARCH_OBJECT_ID is either an ISBN in Book or ISSN in Magazine, and nothing else, or triggers have to be created to enforce this rule. The reason there cannot be a foreign key constraint between the seemingly parent-child table pairs is that one attribute cannot have two mutually exclusive parent attributes. Suppose ISBN is parent of SEARCH_OBJECT_ID and ISSN is also parent of SEARCH_OBJECT_ID, and ISBN has three rows of values 1,2,4 (for the sake of argument I assume single digits for international book numbers), ISSN has values 3,7, SEARCH_OBJECT_ID is 3 and OBJECT_TYPE is 'Magazine'. Business logic is perfectly satisfied but the foreign key constraint on Search_Result.SEARCH_OBJECT_ID referencing Book.ISBN is violated, because 3 does not exist in Book.ISBN. If the database is a data warehouse, you may live without foreign keys altogether, as long as the data loading program is well written. If you prefer not to add any table or column to the schema just for the purpose of maintaining referential integrity, you can always use triggers to implement any rule you like. Otherwise, there are basically two ways to solve the problem. One is to replace SEARCH_OBJECT_ID with two columns ISBN and ISSN: Search_Result ================ SEARCH_RESULT_ID ---------------- .---< ISBN | ISSN >-------------. | OBJECT_TYPE | | ================ | | | | | Book | | Magazine ========= | | ========= ISBN -------- --- ISSN --------- --------- TITLE TITLE PUBLISHER PUBLISHER ========= ========= Diagram 2 ("--<" means one-many) This way Search_Result.ISBN references Book.ISBN and Search_Result.ISSN references Magazine.ISSN. Obviously each row in Search_Result has a value for either ISBN or ISSN but not both. Instead of *replacing* SEARCH_OBJECT_ID with ISBN and ISSN, an alternative can *preserve* SEARCH_OBJECT_ID and simply add ISBN and ISSN, in cases where a common column name will be used in application code. But make sure when records are added to the database, SEARCH_OBJECT_ID is always populated with ISBN if the ISBN column is populated, else with ISSN if the ISSN is populated. This can be achieved by a CHECK constraint on the table. A second approach to this semantically dependent attributes design is breaking Search_Result into multiple tables, each serving a go-between for the represented entity. Search_Result ================ SEARCH_RESULT_ID ---------------- ---SEARCH_OBJECT_ID--- | OBJECT_TYPE | | ================ | | | | | Book_Search | | Magazine_Search ================ | | ================ SEARCH_OBJECT_ID-- --SEARCH_OBJECT_ID --