Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 9 of 9
  1. #1
    Member dinologic's Avatar
    Join Date
    Jan 2003
    Posts
    54
    Member #
    539
    Hello everyone. Been a long time since I've been here but I always keep this forum handy for times like these. I have a pretty simple question (I hope) regarding how to design (not sure if that's quite the right word) and/or implement databases in SQL Server. I'm actually using SQL Express but the question still applies.

    Currently, my company has all of its Intranet applications written in ASP classic using Access databases. It's a complete mess. There are mdb files all over the place, one for each different application. And then there's a central database where any common tables are stored (they're present as linked tables in the various mdbs around the site). I'm currently making plans to upsize some of these database files to SQL Server and start any new projects using SQL Server.

    Here's the question I have: should I create ONE database to contain all tables on the site (could be around 100-200 tables or so)? Or should I be creating a separate database for each application similar to how it's set up now? If I create a separate db for each application, is there a way to join tables that are in the common database to the tables in the app databases when running queries? Or is there a way to replace the 'link' feature that is currently being used in Access?

    Most of the individual mdbs have tables in them that are named somewhat generically...like "main". Obvioulsy, if I wanted to throw all tables into the same db, that would have to change. If it IS best to go this route (separate dbs for each app), am I just supposed to rename all of the tables to indicate the application to which they belong? For intance, "appName1_main", "appName2_main".


    I hope I'm making sense. Any guidance or suggested reading would be most helpful and appreciated. If you need any clarification, please don't hesitate to ask. I'm pretty much just getting my feet wet here and I am in need of some formal education, but I've got a few time-sensitive tasks that I need to accomplish pretty soon. I just don't want to start things off on the wrong foot.


    Thanks.

  2.  

  3. #2
    Senior Member
    Join Date
    May 2003
    Location
    UK
    Posts
    2,354
    Member #
    1326
    Hello.

    I am going on my experience for the company I work for.

    We have 10 databases, 7 on Local Server, 3 on Web Server.

    Out of the 7 databases, 3 of them are used literally all the time. These are for Accounts/Sales/Orders/Stock/etc

    Now, we do not have a lot of web (or indeed software) applications, but we have a specific Database which powers the applications we do have.

    So I would suggest you create a Database for your applications, accounts, sales, etc (if applies).

    Regarding the naming issue, I think it is always a good idea to name the table based on what the table is used for or what the table will index.

    For example, we have 3 tables, Companies, CompanyVessels and CompanyVesselLicences.
    Companies has a=fields like ID, Name, Address, AccountCode.
    CompaniesVessels has a link with companies (ID) and fields like Name, PortOfCall, Agent
    (From the above link we can fathom what Vessels a Company has or what Company a Vessel belongs to).
    CompaniesVesselLicences has a link with CompaniesVessels (ID and Name) and has fields like LicenceName, ExpiryDate.
    (From the above link we can fathom what licences a Vessel has).

    [Note: I work for a Shipping Agent]

    I do not see the point in naming the Tables based on the application that uses them, because what if the application changes its name? Then you have a maintenance issues. By naming the tables based on their purpose your tables already have meaning.

    Just my thoughts

  4. #3
    Junior Member
    Join Date
    Feb 2009
    Posts
    4
    Member #
    18418
    maybe database optimization is needed?

  5. #4
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    If you decide to go with your current model, you should probably go for separate databases for each app. This is less of a performance deal (though it can be) and more of just an organizational deal -- having 200 tables in one place means if you ever have to go database-diving manually, it will suck.

  6. #5
    Junior Member
    Join Date
    Feb 2009
    Posts
    4
    Member #
    18418
    what about runing sql servers on different ports?

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    The simple answer to your question is that if the tables contain related data (no matter how distant the relation), they belong in the same database.

    For example, accounts receivable invoices may belong in the same database as carriers, because invoices are related to orders, which are related to shipments, which are related to vessels, which are related to carriers. And so on...

    However, in the real world, you may choose to separate data that should be otherwise related, for various reasons, such as security (some data public, some only on intranet), performance, etc.

    In the case of an Intranet app that will likely not see much traffic and won't have any portions available on the Internet, I don't see why you can't put everything in one database. It will definitely make your life simpler, as you won't have to worry about periodic (nightly) DTS jobs to synchronize overlapping data.

    My 2 cents.

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    PS - in SQL Server, you can join tables from different databases as follows:
    Code:
    SELECT *
    FROM db1..table1 AS t1
    INNER JOIN db2..table2 AS t2
        ON t1.col1 = t2.col2

  9. #8
    bta
    bta is offline
    Junior Member
    Join Date
    Apr 2009
    Posts
    4
    Member #
    18788
    thx smoseley

  10. #9
    Member dinologic's Avatar
    Join Date
    Jan 2003
    Posts
    54
    Member #
    539
    Thanks for all of the info!


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:54 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com