Results 1 to 14 of 14

Thread: [RESOLVED] Single or multiple databases?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Resolved [RESOLVED] Single or multiple databases?

    Hello everybody,

    I have a question regarding an application I want to develop for my work. We are an engineering company and I want to make like a project manager, organizer and parts list for our projects.

    For the first part of my application I will have data of clients, employees, contact persons for each client with all their information. Also I will have a list of my projects which will contain every information regarding clients, who worked and how much, when was it delivered and so on.

    For all these I am using a single database with tables for each element.

    NOW, there are approximately 300 to maybe 600 projects in one year. For each project I want to create a list of parts which are very many and in many categories like screws, sensors, pneumatics, electrics and so on. They will also contain prices, suppliers, etc... so it will be pretty complex.

    My question is: how would it be best to work? I was thinking that it would be best if every project has it's separate database. But can I create databases at runtime? By doing this would it be safer for backups and stuff like that? I was wondering what would be the best way to manage all these projects and databases? Thank you.

    P.S.

    I am using VB2010 express and SQL Server 2008R2.

    Cheers

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Single or multiple databases?

    one a single database... this is the kind of stuff databases are designed for. I've worked with databases that held years worth of construction project data... we're talking about tens of thousands of items being tracked... for a single project, and there were a lot of projects... I currently now deal with databases that hold all kinds of financial information... years worth of data... the "small" version of the database was only 50GB... the full version was considerable larger...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: Single or multiple databases?

    So your advice is to work with a single database. What about backups? How is it best to deal with them? I mean, in case of a system crash, I don't want to loose anything. How should I approach this?

  4. #4
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Single or multiple databases?

    It would depend on which database you are going to use in regards to what the backup options are! Which database were you thinking of using?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Single or multiple databases?

    As tg says, there's really no reason to use more than one database. An industrial-strength server like SQL Server would have no issues at all. You'd most likely just have a table for projects where each row represented one project. Your parts table would then have a ProjectID column so each part was related to the appropriate project.

    As for backups, that's really up to you based on how critical the data is. You might use a SQL Server Agent job to backup the database every week or every day. I'm no DBA so I don't know whether this would be considered an abuse of the feature but you may also be able to use replication to ensure that you always have an as-close-to-up-to-the-minute copy as possible. You'd only do that for fairly critical data though.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Single or multiple databases?

    you may also be able to use replication to ensure that you always have an as-close-to-up-to-the-minute copy
    I was on a project that went down that route once and would advise against it (not actually my decision). It's quite handy to recover from disk head crashes etc but it does have the problem that any data corruption replicates straight into your "back up" so you can't roll that back. Also, replication comes with all sorts of pain if you want to update database structure.

    If you want to achieve that sort of thing you're better off using an apropriate RAID configuration so you're not worried about disk failures. If you don't need to be quite so up to date then regular rapid incremental backups (I think a minute is the minimum time you can set) combined with a daily full back up is pretty solid.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Single or multiple databases?

    That's the scheme we used... was hourly transactional backups, followed by daily complete backups... so if something happens during the day, at most, all that's lost is an hour's worth of data.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: Single or multiple databases?

    OK. Thank you all for your answers. I am using a service based database (mdf file).

    How is this done: regular rapid incremental backups (I think a minute is the minimum time you can set) combined with a daily full back up...?

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Single or multiple databases?

    I wouldn't go any more frequently than an hour... if you feel like you have to go any more often then that, then that tells me you'll want to re-evaluate your hardware, since you don't have much faith in it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: Single or multiple databases?

    Oh no no. Once a day for a backup it's enough. I wouldn't go further than that. But how should I do it?

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Single or multiple databases?

    once you have your databse setup... setup a maintenance plan for it, which includes the backup process...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: Single or multiple databases?

    OK? But finally how do I make this backup? Do I copy the mdf file in a backup directory? Is that enough? Or are there any other steps to take care of?

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: Single or multiple databases?

    Ok. Lot's of info. Thank you very much. I will make some research regarding this subject.

    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width