Design for Maintaining Static Data

Hii,

I am planning to create a database utility tool that can allow us to maintain the static data let's say user information , staff details and something like that..

My initial thought was to create a generic web screen that can load the data based on the table name entered and from there allowing to maintian the data ..means insert or update..

Now challenge is it should be generic enough to apply for all static tables..

It should also handle relationship between the tables if any ...

What should be the appropriate approach in this case..

A very common problem for web based systems but have not found any readymade solution yet.The approach I would take
1. Design a class DatabaseInfo which read the database usng JDBC metadata and gets the table list and display this list to user
2. User selects the list
3. Use JDBC Metadata again to read the field names of the table along with their attributes.
4. This information needs to be stored in 2 tables. First table would have the list of tables. Second table would have the fields for each of these tables.
This is first time set up.
Now for data entry , show user the screen which has the list of tables.
User selects a table and then display the fields of the table along with their datatype in the jsp.
user enters the values and he data tyoe validation can be done at the server.
To edit the data, user selects the table, the data is displayed in tabular format with radio buttons next to them.
User selects the edit or delete for any row, and the add screen can be populated with this data.

Thanks..

Is there a way to maintain relationship with the tables..

Lets say Employee and Department table are linked with dept id .. Is it possible to detect the foreign key from the metadata and indicate flag in front end on the table's column to indicate that this column is foreign key so make sure for dependency with the parent table?? any thoughts..

Foreign keys are in the database metadata for sure. You might have to query the system tables rather than rely on JDBC for that. You could make any foreign key a dropdown or search on the other table. You might need to populate your own metadata to tell you which columns of the other table to display in the dropdown. Wheee.

Originally posted by Bhavik Patel:
Thanks..

Is there a way to maintain relationship with the tables..

Lets say Employee and Department table are linked with dept id .. Is it possible to detect the foreign key from the metadata and indicate flag in front end on the table's coluhttp://www.javaranch.com/clickServlet?href=http://www.jetbrains.com/teamcity?tc20jr
Team City 2.0mn to indicate that this column is foreign key so make sure for dependency with the parent table?? any thoughts..

Yes you get all that information from JDBC Meta data.

DatabaseMetaData.getImportedKeys(...) AND DatabaseMetaData.getExportedKeys(...)

Cool. That's better than what I said.

Love to learn new stuff here!