Common database for frequently used tables

Posted on 2005-05-03
Last Modified: 2010-03-19

I have been asked to create a database that will contain utility tables that can be accessed from other databases.  I'm not sure how to best approach this.  For example, we may have a table that lists the abbreviation of U.S. states, or common charge codes in our company.  What's the best way to accomplish, so that we have commonly used tables in one location?

Question by:guillotj
    LVL 68

    Accepted Solution

    I guess as you suggested: create a separate db, for example "Utility", and put all utility/lookup tables in that db.

    I've seen lookup tables that were really generic:
    code, keyValue, returnValue

    where code is a different number for each lookup type, for example, one number for U.S. states, another for charge codes, etc., allowing many potentially small tables to be incorporated into one.

    Or you could create a separate table for each (my preference, normally).

    You will also have to deal with the permissions issue: any id that tries to read from those tables will, of course, need access to the db and to the specific table(s).
    LVL 7

    Expert Comment

    seems like an odd request, perhaps the result of poor original design?  the way i see it (correct me if im wrong) is that you have two seprate db's right now with a few tables in common, but the tables contain diffrent data.  the task at hand would be to create another set of tables on an additinal db that both of the exsisting db's can utilize?

    i think that this idea pulls the db's further apart, creates something you dont need?  a diffrent way to look at the problem would be to eliminate one of the data sets and bring the db's closer together.

    if you have two totally independant db's instead of removing something from both of them and creating something new they can both link to you could eliminate the tabels from one of the db and link the second db to it.

    a nice end result might be to have just one db?

    LVL 68

    Expert Comment

    I don't know, I can see some valid reasons for shared tables.  U.S. state tables being a prime example.  That might be needed in many different dbs, but why duplicate it in several places?  Same for a zip code --> city table, area code <--> city table, and so on.

    Author Comment

    >perhaps the result of poor original design?

    Navicerts, you made me lol.  You sure nailed that one...I've been here just a few weeks...people don't even know what a primary key is.  I was going to teach a "brown bag" as we call it, on database one has ever heard the term...poor original design?  At least I have a sense of humor :-)
    LVL 7

    Expert Comment

    I guess its that way pretty much every where you go.  I just graduated, started my first job.  after fixing a certain amount of data errors and begining the plans to redesign the entire db i realized that by ANY definition of the term "database" the company actually didnt have one.

    Author Comment

    How do I link databases in SS, as referred to earlier in this thread?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now