• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need Help in designing database Model design and database?

 
Kishor Joshi
Ranch Hand
Posts: 674
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there

I am working on a web application project for a University.Their are different course Enginnering,Management,Law,Fashion.They also contain sub category like Engineering(B.Tech,M.Tech) and Management(BCA,MCA,BBA,MBA) and Law(LLB,LLM) and Fashion(BAFT).Some courses in them also further contain different category in them.

Their will two types of records one is employee record and other is student record.Employee is further (Faculty,Accounts,Library,Placement,HR) and their are different role associated with Each employee category.

Student their is different types of data(Student Attend Data,Student General Data,Student Accounts Data)

And their is also different category

This is abstract of my project.

I have not posted this code to CODE FOR ME .

I have few question based on these.

1)If a database has 1millon types of record and each year 1 million record is added and they record some of them not all should be there in database for life time(Suppose a Employee or Student is registered in the University then their general record should always be in the system)

2)If at Some Point in future a Client ask to change this product and add some new features in it(How can I backup that data and after update all data should synchronize with previous data)?

3)Which database tools I should use if I have 100millons records to be store in database for 30 or 40 years(Opensource or Propierty)


I try best to explain my requirement

Is there any book read this more

(I am a IT Major and Have worked before this on Some Small Web Project but this time in very big project)


Thanks
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1) I understand this point as the requirement to keep some records in the database indefinitely, while other records should be purged on some condition.

This is rather simple: your application will contain logic to decide which rows to keep and which to delete. Space occupied by removed records will be used up by new ones eventually.

There can be one caveat with indexes: if -over time- you remove some, but not all data from a table, and have an indexed monotonous sequence of numbers in that table (typically primary keys generated by autoincrement or from a sequence), the index may become "sparse" and might benefit from being rebuilt every now and then. I'd suggest to consult your specific database documentation on this point.

2) If the client asks for new features, you'll design the changes needed for your current schema and create some process (it might be an upgrade SQL script, for example), which will make changes to the data. If it's just new features, it will probably just add columns or tables to your model, so your old data will remain as they are. If it is a change to your current model, you need to make the changes happen in the process (create a new structure of tables, populate them with existing data from old tables, and then drop old tables is one, but certainly not only, possibility).

You might want to back up your database right before making the upgrade, in case something goes wrong, and perhaps right after it, so that you can restore the new version. However, most databases nowadays have a backup tool that allows you to restore your database to any chosen point in time covered by the backups, so you might not even need to explicitly invoke backup in this process.

3) Most open-source and proprietary databases can handle pretty large amounts of data nowadays. So, you need to find out how much data is is going to be, and for that you need to estimate the average size of one row. Then multiply that by 100 millions and compare that to the maximum table size of the database under consideration (which can be easily googled up).

When estimating the actual disk space needed for such a database, remember that indexes over a table can easily take up more space that the table itself. The best thing to do is to create all the tables and indexes in your model, populate them with some representative sample of data and measure how much space is needed. And remember to put the actual requirement higher (about twice) to guard yourself against unforeseen circumstances.
 
Kishor Joshi
Ranch Hand
Posts: 674
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks @martin

I am using Hibernate for ORM

from where I should start designng data model of this application.Is there any recommend book or tutioral?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic