Thanks Theodore & Thomas. Here is how i designed my application inititally.
It's for managing material resources. It's for a hardware company who develops some wireless products. As part of thei product development they are buying and using some electronic components (resistors, capsitors, thyristors, semiconductors) etc. Each component has a unique id given by the company itself. The goal of the project is to have a list of all the components they use and based on each product, create a EBOM (Electronic Bill of Material). As the product matures, there will be some changes in the components used in the original h/w design (may be some components added and some might have got deleted ). In such cases, the s/w should be in a position to allow updation to the original EBOM and generate ECN (Engineering change of Note) automatically. There will be two types of users - engineering people and h/w guys who will be creating EBOMs. On the top of these users is the administrator. Administrators job is to create those users (create id and password) and create entries for semiconductors, resistors etc.
For the above project, i selected MS-Access as the DB, apache as the web server and tomcat as the
JSP engine and JDK1.4 + HTML. I selected MS-Access because the company for which i'm developing project is already having ms-access installed on their machines. May be, i might have chosen PostgreSQL or MSSQL freewares so that s/w is scalable. I made a mistake here i believe.
I'm not good at Database designing. What i did was for every EBOM, i created a table programmatically so that generation of ECN will be easier. So, as the products and their versions grow, the number of tables in DB also will grow. Guess this is second mistake. I might have had one single table for EBOM and have a serial number to identify each product. Pls suggest if anybody has a good idea.
For logging, i used simple table having user id, level (to indentify engineering persons and h/w persons) and password. Based on the levels, they are able to see certain fields. For example, engineering person will be able to see few fields of EBOM and he can enter/update only values to those fields and h/w people some other fields. As Theodore suggested, may be i might have used role based authentication rather than maintaining simple table.
The s/w product is 80% ready & working. Most of the features are ready. After demonstrating that application to the company, company felt a threat to their data as it's is internet based application and can easily attached by hackers. Here i didn't know how to convince them. So they went for a small ERP application for their requirement. The catch is, that product doesn't have the feature of ECN (Engineering change Note). So the company's h/w team didn't use that s/w for their material resource planning. Now the CEO is re-considering the application developed by me only to manage their material resources.
As against to this requirement, how can i avoid some pit falls in the current design and how to implement it in a better way this time? I never used controllers / servlets. Just simple JSPs and HTML and some java classes for connecting to the database.
I can even publish my application over the net. Whoever is interested, can run through the application and suggest UI related changes and other changes that makes the user easier to use the application.
Hope i'm not mis-using this forum. If so, pls highlight. I will stop explaining my design.
Thanks to everyone whoever is contributing to the design of this project.