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 |
|
derski
Starting Member
2 Posts |
 Posted - 2009-09-16 : 17:25:55
|
| Hi I have a general design question, I"m designing a database which will talk to 3 different databases for certain lookup information. For example my application will track vendor product lists but the actual vendors are not allowed to be entered by hand but be a loookup to our accounting database. I would like to display the vendor information, such as name, address, contacts but this would be read only. I also need to track vendor associations to in my vendor product list I have to store the vendor id. I'm actually using linqtosql for my data access layer ORM. My approach was to create a local vendor table and have a daily synchronization between the accounting database and my database. I'm wondering if there is a best practice for linking to information across various databases so Should I leave my local vendor table, should I have just views in my database or should I access the vendor information using stored procedures and what the major pros and cons are:Some of the pro's I can think of for doing the table is I don't have to worry about accounting system or network going down and affecting my database, I can add and store additional vendor properties easily, table approach works very nicely with linq. I don't have to worry about vendors mysteriously disappearing leaving orphaned references.The cons would be a daily latency in information and having to run stored procedures everyday to add, edit and potentially delete vendors. I tried finding best practices for this scenario but could not really find anything definitive so is this really kind of per case analysis scenario? or is there some other really good reasons why say one method is preferred over others.Thanks so much.Chris |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2009-09-16 : 21:36:38
|
| Chris, It's very much down to your own requirements and infrastructure. For example, if your network is crap and/or your source databases are prone to crashing, I would have a daily process that replicates this data onto your local DB. On the other hand, if you want 'live' data and your systems are OK, you can use something like a linked server to pick up the reference data you need. There are loads of options on how you might achieve this - again it's down to your requirements on which approach you take.Hope this helps,Tim |
 |
|
|
derski
Starting Member
2 Posts |
Posted - 2009-09-17 : 10:45:13
|
| Thanks a lot for your response, I made the decision based on the fact that our network does often crap out and also user names may mysteriously loose permissions If linking to a live database I'm not always guaranteed to get the data (at least with a daily sync if my procedures fail because someone changed the permissions or deleted the user I"m using to access my data users can still use the app). A one day latency is completely tolerable for the data I'm syncing so I was making the decision myself based on common sense but I wondered if there is a best practice for this scenario. Thanks again.Chris |
|
|
|
|
|
|
|
|
Consolidating data from multiple databases