Post reply

Single database or multiple?

  • February 23, 2004 at 3:42 am

    #101887

    I am a new DBA cum programmer in an University. I have to design software to process students end-semester examination results.

    Should I use single database to store data of all the semesters or one database for each semester (of 6 months). Which strategy is better? What are the advantages and disadvantages?

    Experienced DBAs/Programmers, please help me. What strategy is used in case of Financial Accounting software, one database for each financial year or single database?

    Thank you very much.

  • February 23, 2004 at 3:54 am

    #495111

    Without knowing the exact requirements, I would say that the single db approach is preferable.

    • RDBMS are designed and optimized for handling large amounts of data without losing too much performance, if at all.
    • The overall administration will be easier.
    • Your code to access the data will be much more maintainable if stored in one db and in one table. You can make the semester or the financial year a column of your table and filter that way very efficiently your data.
    • You won't be forced to use dynamic sql to access the data.
    • As you say you work at an university, I would go to the library and search for some books on relational database theory like CJ Date 'An Introduction to Databases, 8th edition' or something similar. No easy stuff, but it should pay.

    HTH

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 23, 2004 at 9:39 pm

    #495239

    I agreewith Frank kalis. A single Database approach is the place to start.

    I don't know why you are even contemplating multiple Database at this stage! The fist stage is to come up with a Conceptual Model and then ascertain.

    Kindest Regards,

  • February 24, 2004 at 12:34 am

    #495248

    Like Frank Kalis and MrSQL said. Probably one db sould do fine.

    The most frequently made worst practise is that one is tempted to go for phisical solutions without even having the slightest idea of what the ERD (entity relationship diagram) looks like or without having any notice regarding the numbers of data one is trying to store.

    Don't be frightend by this. Make the exercise, learn to understand your theoretical and physical needs for data and then go for a dbms-solution.

    If done well, you'll gain on performance , ease of maintenance, less time to master the system for new co-workers, when using use-case-diagrams it even helps to determine which security needs you have , ...

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 24, 2004 at 6:39 am

    #495293

    I work as a programmer/DBA for a college, and you'll definitely want to go with a single database for many reasons.

    Aside from all the technical reasons, schools seem to require endless statistics about their performance across semesters (they call it "institutional research"), and querying out that information would be a nightmare if it's contained in multiple DBs.

    Design your table relationships very carefully.  I made a couple of design mistakes which made it necessary for me to go back in later and modify them ... you want to avoid that if at all possible.

     



    Dana
    Connecticut, USA
    Dana

  • March 3, 2004 at 9:51 am

    #496988

    It seems like this would actually be a pretty simple database setup, only requiring a few tables. I would recommend against having serparate columns for different semesters, though, for a few reasons - it's against standard normalization practices, and it would add a dimension on complexity to period-over-period comparisons. You would be better off to keep the semester data with some variation of a year column and a semester column, and then you could compare semsters with a general query and some parameters. If semester-columns are used, you'll have to rewrite a query every time there's a new comaprison request, whereas a normalized table would allow the same data to be retrieved by just changing the parameters.

    Ryan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply