As I was creating a metadata repository, I came across a doubt. As I'm new to databases, I was wondering if I should add the foreign keys data into the tables that have them (for context, imagine a table that is related to other in a way that it has a FK. Do I need to add this FK into its metadata repository table?). Woudn't that be redundant?
There is no universal best answer. It depends of the context and your intent:
- On one side you could view your metadata repository as an extension of the RDBMS metadata. In this case, you would not need the redundant information. You should even avoid it to prevent inconsistencies.
- On the other side, in a security aware environment, you could consider that the metadata repository is made available to some components or users who do not have the authorisations on the RDBMS metadata (e.g. a development repository vs. production tables, or simply because RDBMS metadata would reveal presence of confidential tables). In this case you would have to accept the redundancy, and even need it to reach your objectives.
- A last remark: it is possible that some foreign keys by design are not declared as foreign key in the DBMS implementation. In this case your metadata could enrich teh technical information with missing design information.
Up to you to select the most appropriate case.