How should I design this table?

Question

Hi,

Because I have a poor memory, I want to write a simple application to store table column information, especially the meaning of table columns. Now I have a problem with my table design. I plan to make the table have the following columns:

id, table_name, column_name, data_type, is_pk, meaning

But this design can’t express the foreign key relationship. For example: table1.column1 and table2.column3 and table8.column5 all have the same data type and the same meaning, how can I modify my table design to express this information(relationship)?

Great thanks!

PS: In fact, recently I'm working on a legacy application. The database is poorly designed. The foreign key relationship is not expressed on the database layer but on the application layer. Now my boss not allow us to modify the database. We just need to make the application working. So I can't do some work on the database directly.

Answer 1

I would just add a column to your design "FK_Column_ID" that will hold a reference to column ID in case of a FK constraint.

The other way will be to create a duplicate of your DB as DBDefinitions or something like that.

Answer 2

Depending on your DBMS, you could probably use comments on the table / column to record the meaning of each one of those columns. Most DBMS allow you to perform some kind of annotation.

If you must have it in your table you have a few choices.

• Free text If this is just to serve as a memory aid, it doesn't really need to be machine readable. This makes it easier for you to read / use directly.
• fk_id Store the ID of the field this foreign key maps into. You could then define a view that pulls in the meaning column from this foreign key.
• Meaning Table Store meaning as an ID into a seperate table and use a view to make it easier to work with.
• Create a document Keep it in a document instead. That way you can print it out and have it handy.

You could try designing a full de-normalized schema for this, but I'd argue thats seriously over-thinking something that's just meant as a memory aid.

Answer 3

Almost all DBMS allow you to attach descriptions or comments to table, index, and column definitions.

Oracle:

COMMENT ON COLUMN employees.job_id  IS 'abbreviated job title';

If you specify foreign key relationships as part of the schema, the database will keep track of them, and will display them for you.

Answer 4

It is not possible to define a compound foreign key relationship with a single additional column. I would suggest that you create a second table to define the foreign keys, perhaps with the following columns:

id, fk_name, primary_table_id, foreign_table_id

and add a fk_id column to relate the fields used in the foreign key relationship. This works for both the single column foreign key and the compound foreign key.

Alternatively, and with some attempt at diplomacy, tell your boss that if you can't fix the root cause of an issue, then the time required to complete the project will be much longer than expected. First you will take some time to implement a work around which will not perform adequately, then you will take more time to implement the fix you should have implemented in the first place (which in this case is fixing the database.)

Answer 5

If you're not allowed to edit the database then presumably you're creating this in another standalone DBMS. I don't think is something you can acheive simply and you may well be better of just writing it up in a text document.

I think that you need more than one table. If you create a table of tables:

id, table_name, meaning

And then a table of columns:

id, column_name, datatype, meaning

You can then create a link table:

table_id, column_id, is_pk, meaning

This will enable you to have the same column linked to more than one table - thus expressing your foreign keys. As I said above though - it may be more effort than its worth.

Answer 6

FWIW, I do this quite often and the best "simple application" I've found is a spreadsheet.

I use a page for table/column defs, and extra pages as I need them for things like FK relationships, lookup values etc.

One of the great things about a spreadsheet for this app, is adding columns to the sheet as you need them, & removing them when you don't.

The indexing ability of a spreadsheet is also v. useful when you have a large number of tables to work with.

Answer 7

I know this does not answer your question directly, but how about using a database diagram?
I also have a poor memory (age I guess) and I always have an up to date diagram on my wall.

You can show all the tables, fields and foreign keys and also add comments.
I use the PowerAMC (aka PowerDesigner from Sybase) database designer, it also generates the SQL script to create the database, perhaps not very useful for legacy databases, although it will reverse engineer the database and create the diagram automatically (it can take some time to make the diagram readable).

Answer 8

I don't see a reason why you should implement some app to store some info there. You can as well use smth like OneNote or any other available organizer, development wiki, etc.: there are tons of ways to store info in such a way that it comes handy when you look up for it in future.

If you can make some inner changes, you can change keys' constraints names to readable pattern, like *table1_colName_table2_colName*.

And at least you can make some diagram, whether hand-made or using some design application.

If all this doesn't solve your problem, some more details are needed on what exactly you need to solve :)