RDMS Design Help

[Pallidrone]

Ahh - that is a fantastic idea Buddha. I am thinking that I will build an original table of keywords and then allow it to grow dynamically when the system is installed.

On a non related to this project, but related to SQL question ;

I kind of know how replication works, but could I have one computer with a specific set of data, another computer with it's own specific set of data and then a main computer that will have the combination of data from computer 1 and 2?

In other words, lets say I have a network of 3 computers. Since I am in a shitty building, I am using a wireless connection instead of a hard line. Sometimes that connection goes down, so instead of connecting to the server all the time, I will have a local database running on each machine. When I am normally 'connected' to the server, computer 1 would update the Widget table with information that is specific to computer 1, and computer 2 would update the Widget table with information specific to computer 2.

Now that I am running separate databases on each computer, I want the server to replicate that information so it now has information from both computer 1 and computer 2 in the widget table. Is that possible, or will there have to be a program that runs to update the table accordingly?

Hopefully I didnt confuse anyone, because I think I may have confused myself.

[=DBA=Ronin]

On a non related to this project, but related to SQL question ;

I kind of know how replication works, but could I have one computer with a specific set of data, another computer with it's own specific set of data and then a main computer that will have the combination of data from computer 1 and 2?

In other words, lets say I have a network of 3 computers. Since I am in a shitty building, I am using a wireless connection instead of a hard line. Sometimes that connection goes down, so instead of connecting to the server all the time, I will have a local database running on each machine. When I am normally 'connected' to the server, computer 1 would update the Widget table with information that is specific to computer 1, and computer 2 would update the Widget table with information specific to computer 2.

Now that I am running separate databases on each computer, I want the server to replicate that information so it now has information from both computer 1 and computer 2 in the widget table. Is that possible, or will there have to be a program that runs to update the table accordingly?

Hopefully I didnt confuse anyone, because I think I may have confused myself.

You should be able to configure something like that. You would probablly want to look into the Merge replication if computers 1 and 2 will be doing anything more than reading data. If they are changing data in any way (i.e. updateing, inserting, deleting, etc), you need the merge so that it can update the central repository once the network is back online. (I've never actually worked with Merge replication, I just know of it and only slightly at best)

However, if 1 and 2 are just meant to read the data that is comming from the central, you can just use distributed replication. Central would be setup as a publisher and 1 and 2 would subscribe to the publication in either a push or pull configuration.

It almost sounds as though you need to use both of these. Merge to update the server from each node, and then a distributor that would update each node with all the other nodes' data.

We use both of these concepts on our platform for different reasons. We have badge readers that we use Merge with as the readers are obtaining data from user input that needs to be written to the main database. In the event of a network outage, the readers can still accept swipes and it just stores them on its local db until the network is back up, at which point it sends the data back to the main.

For our web portal, we use a distributor on the main database that sends "cache" data to each IIS server via a local subsciption. The web portals then read from their local subscribtion databases where applicable to enhance performance. We used to do this via a .NET cache subsystem that would build a local data set into memory upon startup. This was extremely time consuming and the portal would be unusable until the caching was complete, which would take anywhere from 15 minutes to an hour depending on how big the client is. With using SQL replication, we can have a complete system up and running in about two minutes now, regardless of how big it is.

I am still relatively new to replication. I only recently started having to know it because some of our process based reports were programmed to use our old cache subsytem and when client's upgrade to our newer releases that use replication, not all the same data is being stored so we have to reprogram the report processes where needed. In order to test all of it, we have to now setup our internal systems using the replication, so I have been getting a dirty crash course in it.