Database Design Consideration

Here I am going to explain the database paradigm mismatch and consideration in Integration project with the context of “Database design is based on collected data from User “

Last couple of month back I am into the integration of the application It’s like to get the user details consist of state id, district id, Circle Id (in XML format) from the external application that use the set of software and database.

So we started design of database with four tables like

1. state Table
• stateid –primary Key
• state name
2. district table
• distid –primary key
• dist_name
• stateid-foreign key from state table
3. circle table
• circle_id –primary key
• circle_name
• dist_id –foreign key from district table
• stateid –foreign key from state table

4. user table
• user_id-primary key
• user_name
• stateid-foreign key from state table
• dist_id–foreign key from district table
• circle_id–foreign key from circle table

We developed the application based on above data base design with out proper communication between the vendors.

So I have written the code for fetching the circle details based on circle_id only.

Like that get the district details based on district_id.

But when we started integration of the project we got the errors like primary key violation in the district table and circle table while insertion of district, circle details.

So I raised the question to vendor can u give the database design. On that same day I received the data base design but its entirely mismatched with our design Like

1. state Table

• stateid –primary Key
• state name

2. district table

• distid
• dist_name
• stateid

Composite primary key (stateid,distid)

3. circle table

• circle_id
• circle_name
• dist_id
• stateid

Composite primary key (stateid,dist_id,circle_id)

As per the design I can’t fetch the circle details based on the circle_id ,so if I want to fetch the circle details I have to pass the remaining two parameter are stateid, dist_id).

So I have to change more than 100 lines of code in my model and Controller as well as view its take me to couple of days to change the design in db and application.

Conclusion:

Before start the database design discuss with the client/vendor about all the possible of the data and how they are keeping there existing legacy database.So finally database design lies based on data getting from user/vendor.