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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-18 : 10:01:18
|
| Robert Wafle writes "ok.. I have say 3 databases and they all have the same table..tbCompanieswith the same three columns.. CREATE TABLE [dbo].[tbCompanies] ( [CUSIP] [varchar] (50) NOT NULL , [Symbol] [varchar] (50) NULL , [companyName] [varchar] (255) NULL , [SIC] [int] NULL ) ON [PRIMARY]GOI have two databases with my employees table..I want to start linking all my databases together.. but I can't do primary and foreign keys between databases.. I have many tables that i need to use from many different databases..they include:companies (almost every database)employees (my hr database with sensitive info, and my intranet)Advisors (in my intranet application, but needs to be in two more applications)Beneficials (in my intranet application, but needs to be in two more applications)I just don't know what to do!! What is the industry word called when I have problems like this? Should I be setting up databases with ONE table and then subscribing to these databases or tables from the other databases that need to use these tables?Where is the best place for a table when you would like to access it from many databases?? I think it would be a good idea for me to establish that in ALL cases that the tables are READ ONLY unless you're accessing it from another database." |
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2002-03-18 : 15:00:50
|
| I don't know if this is the industry word, but where I work we have one database which holds all tables common to multiple databases.You can't use PK, FK, CHECK constraints, but you could create a trigger where necessarySQL is useful if you don't know cursors :-) |
 |
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-18 : 16:21:43
|
| yeah.. I really like using PK's and FK's.. makes me feel really cozy about the data going into my tables. but I don't want to put all the companies data in one huge database.. (or seperate non-related ones)thats why I am thinking of using replication likely read-only.. But if I use replication, I could have 5 copies of the same table on the same database server.. and that really doesn't make much sense either..I'm reading Chapter 15 on SQL Server Replication in hopes of some answers which may allow me to use the PK and FK.. I feel like I have to build a "basement" (common database) underneath my existing infrastructure which all the common objects exist.Do you have an example of a trigger that works like a PK/FK relationship? Would I need two triggers one in each database? One to be sure that I can't delete a row that has "child" rows, and another to be sure that I can only add data if the "parent ID" is valid? Maybe a third.. one to cascade delete objects.. sounds like I have to re-write a whole bunch of functionality ... |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-18 : 16:29:32
|
| ok.. this just in.. one of the canidates of the tables i need to move into a common database has 14 foreign keys related to its primary key... guess there could be more.. i've really liked the ability for the "database to protect itself".. e.g. I don't have to watch over the programmers or run queries on 14 tables to see if someone made a mistake and forgot to enter the correct number into a foreign key.. as the database doesn't allow it and throws an error.. |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-18 : 16:31:42
|
| You said your 'common' database was read only, so you don't need the delete triggers on that side....Jay<O> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-18 : 16:54:00
|
| What's the difference between lots of little databases and one big database?DavidMTomorrow is the same day as Today was the day before. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-18 : 16:59:48
|
| This is just an opinion, and I'm not sure it will help you figure this out. I hope so, here goes:I would recommend that these multiple databases be consolidated into 1 database, if they have a significant amount of common information. It really doesn't make sense to duplicate the same tables in 3 different databases or try to replicate them, because you'll have synchronization problems at some point.It may be possible to set up 3 databases so that the common tables enforce integrity through triggers, but this is only half the problem. If the common database gets hosed somehow, and is not available, the other databases also become unavailable. And if you need to restore the common database independently of the others, the triggers will not maintain data integrity (newly inserted companies since the last backup would no longer be valid) It's very easy to have this, or something even less drastic, compromise the integrity between the tables and databases.The question, to my mind, is which priority is the highest of these: security, functional separation & encapsulation, or data integrity & consolidation. It's truly rare to have a situation where ALL 3 carry equal weight and must be stricly enforced.1. Does the HR data ABSOLUTELY HAVE TO BE kept separate from the other tables? (in other words, the security CANNOT be accomplished with logins and user permissions that restrict access to the sensitive tables) If no, then combine them into one database.2. Does each database HAVE TO EXIST as separate entities for performance or security issues? If no, then combine them into one database.3. Does the data ABSOLUTELY HAVE TO BE consistent across all of the tables? If YES, then you really should keep everything in one database.The first 2 questions are pretty easy to accomplish (answer "yes") while still using 1 database. Logins and roles can provide security, and filegroups and indexing can provide performance enhancements. However, question 3 is MUCH HARDER to accomplish with multiple databases; no matter what you do it will be much less than ideal and you'll most likely miss something that will bite you in the ass later. And since you have a desire to use foreign keys instead of triggers, I think that data integrity is your most important consideration.HTH |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-18 : 17:17:31
|
| thank you all for your posts!!! I will attempt to respond to them in order.jay - yeah.. your right.. if i wanted read-only then I wouldn't need the delete triggers.. you caught me.byrmol - i can have one database on one server and one server only. I can have two databases on one or two servers, distributing the load... also, with one database I have data integrity. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-18 : 17:27:30
|
quote:
byrmol - i can have one database on one server and one server only. I can have two databases on one or two servers, distributing the load... also, with one database I have data integrity.
It was a question for you, not me!DavidMTomorrow is the same day as Today was the day before.Edited by - byrmol on 03/18/2002 17:28:06 |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-18 : 18:06:57
|
| robvolk - excellent feedback man.. you have truly provided the best answer so far.. I'm riding the "robvolk" bandwagon now.. but I have more questions if you would be so kind?I wrote something but it amounted to nothing but babble. How many tables is "too many tables".. and if I can think of a PK/FK relationship is that enough to say its related and just keep building a bigger and bigger monster database? Do you use any special tools to restore data from a backup at the table level? Say BackUpExec for SQL server? I've really not had any problems with having to restore data before as we haven't lost anything in 2 years.. but if I keep adding more and more tables to the same database, having more and more programmers.. something is bound to happen.. |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-18 : 18:08:51
|
| brymol - ok.. i would guess the data in the each databases is not related to the data in the other database. otherwise it would be in the same database. |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-18 : 18:20:47
|
| ok .. this is hard to put into words..but i feel like I have three different buisiness models in my company. so I am not comfortable with the idea of one large database for everything. I think I can consolodate some but not all.. 1: stock survellience2: sending bulk emails to 1,000 email addresses with end-of-day-pricing3: rummaging through the garbage of SEC filings to create usable output in a database format.Should three seperate buisness models be supported in one monster database?They are all related to two base tables.tbCompaniesTwo of them are related to:tbInstitutionstbInstitutions contains "Mutual Fund Companies and Mutual Funds".. which is pretty much what stock survellience is all about.. now, lets throw an interesting "left-hook" into the question.. think of this abstractly or from your own experience.. i wonder what would happen if the company decided to "sell" one of the three buisinesses to another company.. how would they do that without copying the one huge database? :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-18 : 18:58:55
|
| I think you'll have to post all your table structures and relationships before we can offer any more advice. There's really no limit on tables (2 billion is the max) but how you use them will make a difference. You can have 10,000 tables in a well-designed database, and only 10 in a poorly-designed one; it's hard to tell how many is "too many". And not knowing how they relate to each other is death (with what you posted I can't see any need for--or relationship to--HR info whatsoever!)As far as the three different businesses, you can always remove the tables that they don't need and copy the database. At that point there really isn't a relationship any more if the business gets sold or transferred. Then it would become a B2B issue. You can also create database roles for each business type, and if something changes you can revoke rights within that role, and protect the sensitive information they don't need anymore, without removing it from the database.Let me ask you this: how often would company info be added, modified, or removed from the Companies table? What about the Institutions table? I don't want to sound like I'm backtracking, but it sounds to me that now the separation of the business model is more important than data consolidation. One of them has to give.Backups at the table level are not a good idea, because again, data integrity is the most important thing. The whole database is an entity that should be treated as one thing. You can restore individual tables through some workarounds, these workarounds are actually good things because they force you to check integrity before proceeding.As far as securing the database, you should restrict rights as much as possible. NO ONE should get any CREATE or DROP permissions unless you review them and justify it. Programmers can always work on a development copy of the database with full permission; after their work is reviewed THEN it gets copied over to production.Also, don't make SQL Server your mail server or mail sender. It can handle 1,000 a day, and even more, but it will hold up the machine. And the email burden will grow too. Find a way to output the reports and have the mail server pick them up and send them. |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-19 : 16:02:44
|
| robvolkThank you again for your help. I'm going to put all our "stock survellence" datavases into one database. I can tell you that the EDGAR database will ideally feed the stock survellence database with data.. So, I'll work that one in as soon as I can as well. The big problem we have today is that there are no "keys" to relate data between SEC information and our information. e.g.SEC has:Fidelity Fund One 12345Fidelity 11111We have:Fidelity Fund 1 26FMR CORP 22.. and I've got to match 20,000 of these manually I guess.. because I have been asked to make all the names appear the SAME throughout the different applications (one more reason to put it all into the same database).. but I can't put the companies from one database into the same table as the other database.. I have to relate them.. 26 matches 12345 and 22 matches 11111... because I can't match them without someone manaually going through and matching them... so there has to be some sort of painful transition.. Anyways.. I have an exchange server which handles my SMTP mail, but currently the vbs script that creates the HTML, converts it to PDFs and sends emails runs on the SQL box along with IIS.. The total load of that aplication is 35 minutes a day.. email load is about 5 minutes of that.. nothing huge and the growth is actually somewhat controlled. I don't use xp_sendmail because I found it to be very slow. I am using ASPEmail by "persists" becuase it is much faster and I am evaluating using its "Asyncrounous Queuing" abilities which allow my script to run very fast and drop messages into a queue for the service on the exchange server to pick up... very nice. I am also thinking about SMTPMail by "softartisans" as well.. do you have any experience with these?I'm doing what I can to move IIS and SQL onto at least two different machines... Where could I go to find a good book on "good development practices".. or go to take a class? Where can I find a good mentor? I read lots of books but sometimes I just need to ask questions and I resort to these forums but I've never met anyone from the forums and gotten a true sense of community and teamwork.. SQLTeam rocks though.. I have lots of questions.. I obviously don't expect you to answer ANY of these.. but where do I go to learn answers to these questions? How do I create a production database when my database take 75% of my disk space and my boss wants me to add features and I can't exactly copy the database or get more disk space? How do I get $$ from management to make things work? How do I say "NO" when they ask me to do something without the correct tools? How do I make it clear they are asking me to build them the best house on the block, but I've never built the best house on the block before.. and I have just enough nails to build the house and I can't bend a single one?I need practice each and every day.. (so do my programmers) and they need a development enviornment. How do I create that with limited resources? and they ask me to do it with just one server and no playground because we haven't had anything serious happen in over a year. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-19 : 16:52:23
|
quote:
because I can't match them without someone manaually going through and matching them... so there has to be some sort of painful transition..
Ahhh, if I had a nickel for every one of these I've had to do!  As daunting as that will seem, it is best that you devote as much effort as you can to get that done as quickly as possible. I usually like to use guerilla tactics for stuff like that, meaning I come in on a weekend and just slog through it as fast as possible. Since you've got some people to help you you should be able to knock it out fairly quickly. You can't avoid the roll-up-your-sleeves-and-dig-in aspect of the work, you might as well get through it ASAP.I haven't used the email components at all, but have a look at these articles:http://www.sqlteam.com/item.asp?ItemID=5003http://www.sqlteam.com/item.asp?ItemID=5908I think you're on the right track with the email, so don't worry about it too much.quote:
Where could I go to find a good book on "good development practices".. or go to take a class? Where can I find a good mentor? I read lots of books but sometimes I just need to ask questions and I resort to these forums but I've never met anyone from the forums and gotten a true sense of community and teamwork.. SQLTeam rocks though..
Just keep posting here on SQL Team! There's a TON of superb experience, and I can tell you that you're not going through anything we haven't gone through already. Just keep on posting if you've got a question, but do try to research it a little first. Check SQL Team articles, search the forums here, make sure you look through Books Online, etc. If you have ASP questions these sites are a good place to go for ideas and answers:www.4guysfromrolla.comwww.aspfaqs.comwww.learnasp.comwww.15seconds.comwww.aspalliance.comquote:
How do I create a production database when my database take 75% of my disk space and my boss wants me to add features and I can't exactly copy the database or get more disk space? How do I get $$ from management to make things work? How do I say "NO" when they ask me to do something without the correct tools?...I need practice each and every day.. (so do my programmers) and they need a development enviornment. How do I create that with limited resources? and they ask me to do it with just one server and no playground because we haven't had anything serious happen in over a year.
There's no real mystery to create a development SQL Server. Find any machine lying around that meets the minimum requirements, and install the Developer edition on it. If you can't get the Developer edition, get the Evaluation copy! I installed SQL 6.5 on a 200 MHz Pentium with 32 MB of RAM (my "production" server was a 400MHz machine with 320 MB of RAM!) I developed databases on it. I even tested web pages on it (running Win95 and Personal Web Server) This was a little over 2 years ago! Don't make the mistake of thinking you need a hot machine or a clone of your production machine in order to write a database app. In fact, IMHO you are better off developing on a shitbox because it will help you develop more efficient code. If you can get it to run fast on a piece of crap, it will only be that much faster on the real machine. Any computer that can run NotePad can be used to develop web and database applications! As far as saying "No", it's pretty easy. "No" has fewer letters than "Yes"! Seriously, if your managers totally get on your case and you absolutely cannot do it, tell them "No". If they knew how to solve the problem themselves they would never have hired you in the first place. You will be in FAR worse circumstances if you lie and say "Yes", and then weeks or months later don't have the job done. Oh man, do I know THAT really well!You are their expert on application development, and in your professional opinion they have not provided the tools you need to do your job. They are perfectly free to do it themselves with the same resources! This is not vicarious bravado on my part. Whenever I've had to say this to someone, 90% of the time is was simply to call the bluff of some idiot manager who overstepped their knowledge, authority, budget, whatever. The last thing they want is for you to tell THEIR boss that they screwed up. Whenever I had to deal with a smart manager who even half understood what I do, this NEVER became an issue.Another thing: don't give YOURSELF reasons to not get the job done. Just do what you can with what you have. Build on ANY success, no matter how minor. If people see that you can accomplish something with nothing...especially on a consistent basis...you will soon find that you will get whatever you ask for.quote:
How do I make it clear they are asking me to build them the best house on the block, but I've never built the best house on the block before.. and I have just enough nails to build the house and I can't bend a single one?
Same way you build your 5,000th house, having unlimited resources: pick up a board, a hammer, and a nail, and drive it in. Repeat until the house stands on its own  If you approach it as something that's that simple, it will become that simple.HTH |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-03-20 : 14:05:59
|
| Thanks RobVolk.I appricate the kind and good advice.I'm going to go try to full-text index a 64GB table now.. It took me 24 hours to load all the data.. I wonder how long it will take to index it or search it.. |
|
|
|
|
|
|
|
|
where is the best place for a table when you need it in many databases?