CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designrelational-database
Samir
Samir

Reputation: 283

Foreign key on identifying relationships

Let's start by a quick definition of an identifying relationship that I found on the web :

An identifying relationship is when the existence of a row in a child table depends on a row in a parent table, this means the primary key of a child table contains a column that belongs to a foreign key referencing a parent table.

My example: I use SQL Server and my example is the following :

  1. I created a data table to store data about schemas (metadata), and yes, sys views information are not enough in case you are wondering :)

    My metadata table is called Schemas_Metadata and has a Name column as the primary key.

    Schemas_Metadata
----------------
PK (Name)

  2. I created another metadata table to store data about tables and my metadata table is called Tables_Metadata and has a Name column and a Schema_Name column (foreign key referencing the Name column in the Schemas_Metadata table) and a composed primary key (Schema_Name, Name). This is an identifying relationship.

    Tables_Metadata
PK (Schema_Name, Name)

  3. I created another metadata table to store data about columns called Columns_Metadata and it too has a Name column, a Table_Name column and unfortunately a Schema_Name column (Table_Name, Schema_Name are a foreign key referencing the Schema_Name,Name columns in the Tables_Metadata table) and a composed primary key of (Schema_Name, Table_Name, Name). This also is an identifying relationship.

    Columns_Metadata
PK (Schema_Name, Table_Name, Name)

My question:

At the third level, do I need to create two columns Schema_Name and Table_Name in Columns_Metadata table and do I have to create columns I don't need each time I create a foreign key on an identifying relationship, or is there a better way to do it ?

Upvotes: 0

Views: 482

Answers (1)

George Menoutis
George Menoutis

Reputation: 7240

First, let me express my opinion that the already existent design of the information schema and the sys schema are very elaborate, in-depth and intricate compared to your question which, if we forget for a moment that its CONTENT concerns metadata, is a very simple case of table design. Thus, I doubt you need to make another schema.

To answer your question: I would create an id column on Tables_Metadata, put the PK on id alone, and add a check constraint unique (schema_name,name).

Then, The Columns_Metadata table need only have two fields: Name, and Tables_Metadata_id FK to Tables_Metadata.id

Upvotes: 1

Related Questions