Database Design Issues - One NT Server, Multiple Entities

[PaulMacFarlane]

I'm getting a lot of pressure from users of one of my products to get a web version going.  Basically 90% of my inqueries are for a web version.

So, initially I was thinking of having a NTS login page where once logged in it would set some session variables for company ID, and Database Path.  This would point their data access to a separate set of TPS files.

Alternatively, restructure the database with an EntityID in each table and filter data using this EntityID.
In this case probably go with a SQL backend.

My concern is how will NTS behave when I'm changing the path/filename of tables constantly.  Every page load, etc.


Thoughts, experience, suggestions?

Thanks,
Paul

[kevin plummer]

NTS can handle different tps or SQL data sets no prob.

Avoid adding Entity ID as this add's a lot extra overhead (everything needs to be filtered) and other potential problems like backup\restore that you don't need. This method also doesn't scale very well.

HTH's

[Bruce]

I agree with Kevin.

>> My concern is how will NTS behave when I'm changing the path/filename of tables constantly.  Every page load, etc.

just fine.
there's an embed in WebHandler (ProcessLink method, before Parent call) to do just this.

cheers
Bruce

[PaulMacFarlane]

so it's just a matter of issuing a SetPath in the embed?

No buffers getting mixed up? 

tables all need to be threaded I assume?

[PaulMacFarlane]

Kevin,

You said that using an EntityID doesn't scale well.

I'm just wondering how the big boys do it?  I thought they all ran everything into a large SQL database.

If I used the EntityID method it would definitely be in SQL.

My next thought is if I use separate "databases"
folders/paths for TPS files or
databases in SQL

which would have a faster response to the user?

has anyone done this comparison?

[kevin plummer]

I have no idea how the big boys do it. I can only imagine if there were a group of interrelated companies that it would be more convenient to have all their data within one DB for consolidation purposes etc. It sounds like a lot of potential headaches to be adding no-related coy info into the same DB. Not to say you can't do it that way, it's just considering NTW does not have any issues connecting to different SQL DB's why would you bother when there are more cons doing it that way than creating separate DB's?

I would deploy whatever you are currently deploying to your users which sounds like TPS. The last thing you need is to try to convert to Web and SQL at the same time. Down the track if you wanted to convert to SQL it will be simpler.

To answer you question about response time, it depends how complicated you app is. The speed advantages in SQL would be the same as what you would get on a desktop app. If you don't inject Prop:SQL statements then some stuff actually runs slower. But if you inject Prop:SQL into process's and reports it will run much much faster. That's my experience anyway...

[Bruce]

>> You said that using an EntityID doesn't scale well.
>> I'm just wondering how the big boys do it?

They spend a lot of money.
But I imagine they also segment their databases where possible. Single databases that span multiple servers are complicated to set up, and expensive (from a human point of view) to keep going.

On the other hand if you are creating distinct data sets where the data is naturally segmented than it becomes very easy to add more servers, put different companies on different servers, make backups, and so on. Unless you absolutely need all the data squished together in the same DB, I wouldn't go that route myself. Splitting data across databases seems to make a lot more sense.

>> folders/paths for TPS files or databases in SQL
>> which would have a faster response to the user?

response time for the user is similar in both cases - with the usual endless caveats on size, reliability and so on. Both are fast enough, so I think speed should not be the determining factor here in the short term. It's also fairly straight-forward to change from one to the other, so you're not locking yourself in whichever way you go. (TPS can be helpful when you're learning, if only because it's a lot easier to send me examples.)

Cheers
Bruce

[Robert Iliuta]

Hallo Paul,

I have a NetTalk WebServer that serve around 130 users and more then 300 database... Some users have access to 4 or 5 data base and they change the data base on the fly. Very easy, fast and very stable!
You will find tips on this forum regarding how to setup data base for multiple database, change the path... etc
I keep a data base for every company they work. I use TPS. I do also some tests with SQL but not to much difference so far. For the moment I see no reason to move to SQL.
For me NTWS platform is very stable and fast!

Good luck!
Robert