Can you use multiple databases? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Can you use multiple databases?

Author

Topic

jeff.nelson
Starting Member

20 Posts

Posted - 2002-05-13 : 16:34:14

Can you access information in one database from another doing something a simple as join query? I'm trying to create a normalized DB but it's gotten so big table wise that I'm considering housing half the data in one DB and the other in another DB. Which means they need access to data in each other. I'm going about this all wrong or should I keep it all in one DB.

If it can work how would one access the tables in one DB and the tables in another DB using some sort of INSERT, UPDATE, or DELETE statement.

TIA, Jeff

chadmat
The Chadinator

1974 Posts

Posted - 2002-05-13 : 16:44:37

I would probably have to say you are going about it wrong. There is no reaso you should have to create another DB.

However, to answer your question:


Use Northwind
go
select * from Pubs.dbo.authors
go

HTH
-Chad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 16:47:26

The big problem you face with putting tables in another database is that you can't use foreign keys. The only way to enforce data integrity in that case would be to use triggers. Not impossible, but less than optimal.

When you say "gotten so big", is it the amount of data, or just the number of tables? If it's the number of tables, yes, it might seem daunting but you'll get used to it. Unless you think you're normalizing the tables too much (there is such a thing as over-normalizing, even though someone will insist otherwise) If all or most of your queries/procedures have more than 5-6 joins to get a meaningful result, you might want to consider de-normalizing a little.

If the amount of data is becoming too much, you can create the database on multiple filegroups on multiple hard drives, and spread the tables out onto these multiple filegroups. If this is the case, read the entries in Books Online about filegroups, to get the maximum benefit from them.

What kind of data are you modeling anyway? What are you storing in this database? How many tables do you have exactly?

jeff.nelson
Starting Member

20 Posts

Posted - 2002-05-13 : 17:03:57

The database is to be for an online tax filing website. The major portion of it is done which has a table for each state location, bank data for each state location, transaction information for the primary account, county lists for each state, and a primary account table. So far I've reached around 11 tables, but I've only set up about 2 complete states. I will probably reach over a 100 tables by the time I'm done doing state data.

The problem I run into are the reports users will be creating. There is a possibility a user can create a multiple filing report. (IE One report that has multiple counties involved). I haven't been able to find a way to setup a database scenario to account for this accept to create a single table for each county in a state. Some states must have over 100 counties. Working with FL & GA right now I could estimate around 500 tables just for counties alone!! I'm bashing my head to develop a work around. So I'm tossing out the idea of using multiple databases so to not confuse my ass putting it all in one DB.

Hope this helps, Jeff

BTW if you are interested in checking it out in BETA form you can browse through it at FP-LINK.COM. The layout hasn't been designed for it so it may look simple, but there is a ton of code behind it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 17:44:04

quote:
The major portion of it is done which has a table for each state location, bank data for each state location, transaction information for the primary account, county lists for each state, and a primary account table.

First thing you should NOT do is create sets of tables for each state. Make one States table. Make one Counties table. One Banks table. (I think you've done this already) But you do NOT want to have this:

tblFlorida_Counties
tblGeorgia_Counties
tblAlabama_Counties ...etc.

If you are heading in that direction, STOP NOW. You will have to rethink your design.

I can tell you from personal experience with a tax module that was incorporated into a database system I worked with (didn't design or modify though) It was for sales tax and excise tax charges, though, but it might give you some ideas.

States, Counties, and Cities/municipalities were assigned codes. Each state had a state-level sales tax as a default. Each county had its own sales tax, but also had its own state tax column. This allowed overrides to the state tax rate for a particular county, if it was applicable. The same applied to a city/municipality. While each level of government had its data in its own table, and had default tax rates, each rate could be overridden at a lower level.

The same applied to the rules that determined tax exemptions (certain sales amounts accrued more tax, and some amounts no tax at all) These rules were stored in another table keyed by state, county, and city. If a tax rate applied for amounts between $10,000 and $25,000 for example, there would be specific rows in the table, like this:

State    County      City         LowerLimit     UpperLimit     TaxRate
NY       Suffolk     Riverhead          0.00        9999.99      0.0825
NY       Suffolk     Riverhead      10000.00       24999.99      0.0800
NY       Suffolk     Riverhead      25000.00           NULL      0.0825

The queries involved would look for these amounts and apply the correct tax amount to the sale. 95% of the time they were accomplished with simple joins, but all of the rules were strictly data/table-based. Nothing required any special procedural processing, meaning that there were NO rules that COULD NOT be expressed as data in a table.

You could apply the same idea to the bank table, account info, transactions, etc. Every rule should be expressed as data, as much as possible (some can't, but you'd be surprised how many can) If a bank has different rules for different states, have a State column in the Banks table, and store a row for each state with a different rule. Extend this to Counties if need be.

The point is, you're NOT creating a special table for Blah-Blah Bank in So-And-So State in Whatchamacallit County. And you definitely don't want to split each state or county into its own database.

It may not seem like it, but a structure like this can easily support multiple state or county tax assesments, all in a single query. The point is, from a database perspective, it is all reduced to rows in tables, which are JOINed together, with little or not additional processing required.

Considering how complicated State and Local sales tax rules are, the fact that some software vendor found a way to do it should inspire you! Trust me, income tax is NOTHING! I'm NOT KIDDING, there are parts of Westchester County in NY that have different sales tax depending on which side of the street you are on! And this tax module had it all nailed, 100% accurate!

I can't give any other advice without seeing some more detail on the exact table structures you have now, or the kinds of things you need to support, but I'm totally confident it can be done this way. If you need any other help, just post it here!

Good luck!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-13 : 17:46:32

Jeff,

100 tables is not very many tables at all...

If you add another zero on that number then you might have case...

DavidM

"SQL-3 is an abomination.."

jeff.nelson
Starting Member

20 Posts

Posted - 2002-05-14 : 09:56:21

Thanks for the help, but I'm confused on one point. I hope you can understand this...

Let's say a user wants to create 1 report that has sales in 5 counties. So he will enter in data for each county screen and in the end it will consolidate into 1 report. How do I store the data that was entered for each county? The consolidation is the easy part and saving it by state is simple. This is why I considered a 1 to 1 table to county scenario. However I will have some many tables in the end result that I would loathe just looking at it and I'm looking for a better way.

Specifically I'm dealing with GA as my first state (It is also where I'm located). Breaking down by county I could theoretically produce highly detailed statistics that generate total sales in a county for 6 to 12 month periods, total business sales in all counties for 6 to 12 months, etc...

Thanks, Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-14 : 10:17:20

CREATE TABLE Sales (
State char(2) NOT NULL,
County varchar(30) NOT NULL,
AccountNumber int NOT NULL,
SaleAmount money,
SaleDate datetime DEFAULT (getdate()) )

This will set up a sample table structure for you, and the following will give you some sample data:

INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Hall', 123, 119.95)
INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Forsyth', 123, 219.95)
INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Forsyth', 123, 119.95)
INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Fulton', 123, 119.95)
INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Fulton', 321, 19.95)
INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Fulton', 321, 29.95)

Now you can get a full report on all sales in GA:

SELECT Sum(SaleAmount) Total FROM Sales WHERE State='GA'

You can summarize by account number:

SELECT AccountNumber, Sum(SaleAmount) Total FROM Sales WHERE State='GA' GROUP BY AccountNumber

You can summarize by county:

SELECT County, Sum(SaleAmount) Total FROM Sales WHERE State='GA' GROUP BY County

Or both:

SELECT County, AccountNumber, Sum(SaleAmount) Total FROM Sales WHERE State='GA'
GROUP BY County, AccountNumber

Granted these are very simple examples, but they are very easy to extend.

The idea is not to think in terms of "I have a county and I want to track sales in it", but instead, "I have sales I want to track, and these sales have a county in which they occur". The county becomes an attribute of a sale (don't know if that helps!) Another way to think of it is this: if you have a county that has no sales at all, why create a table for it?

Another point too: if you kept counties as separate tables, and there were no sales in that county, how do you report that? If you don't have a table for it, you can't. If you have an empty table for it, you have no results to report! By keeping the Sales table the way I described, you can do a simple LEFT JOIN query to a Counties table and still perform the report aggregation. Each county is listed, and if there are no sales, you don't have to change the formula from the ones listed above.

HTH. Keep posting any other issues that come up, this is starting to interest me!

jeff.nelson
Starting Member

20 Posts

Posted - 2002-05-14 : 10:53:12

GREAT IDEA!! Thanks I'm gonna start testing that idea right now! BTW how do I do a conversion to money during an insert? I've got a value of 30,000.00 and would like to insert it as MONEY (via stored proc) only to get the following error

Disallowed implicit conversion from data type nvarchar to data type money, table 'ga filing.net.dbo.report_data_GA', column 'Total_Sales'. Use the CONVERT function to run this query.

Edited by - jeff.nelson on 05/14/2002 10:55:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-14 : 12:18:22

You could use CONVERT() in your INSERT statement:

INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Hall', 123, CONVERT(money, '30,000.00') )

I'd recommend against doing the conversion though, because it's just overhead. If you're trying to insert the amount from a data entry screen, just don't allow commas in the input box. If you enforce this at the data entry point, you avoid the possibility that someone enters alphabetic characters too, which will crash the CONVERT() function. If the value is strictly numeric, you pass it without the single quotes or commas:

INSERT INTO Sales (State, County, AccountNumber, SaleAmount)
VALUES ('GA', 'Hall', 123, 30000.00)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources