webapp & db design

[amadeus316]

I have an idea and here is the breakdown

400 locations
1 server per location
different data in the local db at each location
___________________________________________________________

Now there has to be away for all the dbases to merge into 1 master db


Well suggestions guys i need them honest only

 

[ameoba]

Care to be a little less vague?

 

[Kris]

I say create an access database at each location. Use an ASP.Net application to interface with a VB6 DLL to manage each database.

At exactly 2:35AM EST, have your databases connect to a master Access database via PHP and syncronize the data.

Done

 

[amadeus316]

ok Kris

Thanks for the suggestion. Any others...

 

[Robizzle01]

400 locations sounds like a rather large project. I'd get professional advice.

 

[tim_m]

use flat text files

 

[v6maro]

why not have 1 global database with 400 prefixed or suffixed tables, then a trigger or nightly process to insert / update / delete rows from the master table?

Is there any reason why all 400 servers cant talk to the same database?

or like the guy above said, export to flat file then import to the main db through some process.

 

[mikeblas]

It's impossible to provide any meaningful advice without at least a few more details about the project.

 

[hokatichenci]

It's impossible to provide any meaningful advice without at least a few more details about the project.

Agreed. Not to mention here that for 400 locations you'd probably want maximum availability, which would be more than a "master" db server and would probably be a HA db cluster. Then you would also have to hope that the power never dies (don't say N+3 can't go down), or that the internet connection also won't fail. Redundant clusters over multiple locations would be a wise decision for any real project.

Somehow methinks this is one of those school assigned projects though.

 

[tim_m]

or like the guy above said, export to flat file then import to the main db through some process.

that's not what i meant

 

[v6maro]

that's not what i meant

you may have been joking but some companies do things this way, esp those with mainframes.

 

[Tytalus]

I have an idea and here is the breakdown

400 locations
1 server per location
different data in the local db at each location
___________________________________________________________

Now there has to be away for all the dbases to merge into 1 master db


Well suggestions guys i need them honest only

Mike already made the point, but without additional information this is somewhat vague on description.

I would read some of the Microsoft DB materials on Replication. There are many different types of replication available.

I couldn't find the reference I've used in the past for understanding replication, but this is a decent start (kinda' a hard read, but it is a pretty good overview): http://msdn2.microsoft.com/en-us/library/ms166367.aspx . I also recommend searching for "SQL replication types" and read up on a few of them. I believe the "wheel and spoke" method would be the type you are seeking. (It's got a ton of different names, but it's the same regardless)

 

[SirRuka]

you may have been joking but some companies do things this way, esp those with mainframes.

QFT... they are also the companies whose mainframe apps can only accept upper-case data. Such as the company we've had to deal with; they just couldn't understand why it would be a problem to send us back data we had encrypted but in all upper case.

 

[mikeblas]

Agreed. Not to mention here that for 400 locations you'd probably want maximum availability, which would be more than a "master" db server and would probably be a HA db cluster. Then you would also have to hope that the power never dies (don't say N+3 can't go down), or that the internet connection also won't fail. Redundant clusters over multiple locations would be a wise decision for any real project.

Maybe, or maybe not. Some data is disposable, and the volume might be so low that an advanced solution isn't really necessary.

I guess what you're suggesting is probably true, but without any details we're all just guessing.