Where to put user data?

[Keith]

Sorry for going over old ground, I know that this has been discussed before, but . . .

Suppose I have a single table which can hold records by user.  There seems to be two approaches to architecting this

• to hold all of the records in a single table keyed by user id
• having a sub-directory for each user

If there was only a 'few' users - say 1000 and a few records per user, say, 10, then a single keyed file looks like it would work ok.  But 1000 subdirectories - is that feasible?

On the other hand, having all of the records in a single file is a bit scary (can anyone provide solace for this ill feeling!)

So, what's best practice?  What has been done and what are your experiences?  Are there any rules of thumb?  And lastly, what's the best way of going the 'sub-directory' routs where you could have lots of sub-dirs. active at any time - how does that work?

Thanks

Keith

[kevin plummer]

with that number of users and few records I would favor 1 DB using SQL not TPS

[Keith]

Kevin

You've raised an interesting point.  The problem is that when we publish this application on the Internet (its not an in-house Intranet app) we don't know what the uptake will be.  We hope it will be successful and that could mean 1000 users or more - who knows!

So, what I don't want to do is to launch with TPS files and find shortly that there is a lack of robustness or control.  So, a couple of questions:

• Is it your opinion that with hundreds of users that segmenting the data via sub-directories is unworkable?
• Is storing many users' data in TPS files and relying on filters to present the correct records not a robust approach?
• Is a SQL database significantly more robust than TPS files (it is unlikely that we would approach the 2GB limit)
• Would you recommend MySQL?

Thanks

Keith

[Devan]

Keith,

We went through a similar thought process here with a project a couple of years ago.  We went with the single SQL database with unique user IDs, and glad we did.

MySQL is our database solution for nearly all our NetTalk proejct now.  We only use MS-SQL whenever the clients specifically request it to suit their network requirements.  We never use TPS anymore for NetTalk sites.

One of the biggest benefits we found with a single database table, was that it made it really easy for US to administrate and to check user activity levels etc.  We could easily tell which users were more active than other, plus run other periodic metrics and statistics in the database because all the data was in one place.  Trying to do that over a thousand separate sets of data would have been a nightmare.

Hope this helps.

Devan

[Keith]

Devan, thanks for your comments.

Yes, it is obvious now that we need to have one repository for the data.  The other approach - to have separate repositories would only make sense (for a single centralised app) if there were a few large users that were unrelated.

So, the question then is TPS or SQL and I can see that the preference is for SQL so some qualifying questions if I may regarding a SQL choice:

• Is MySQL better than TPS because of robustness, flexibility, growth possibilities and better customer acceptance?
• Do you deploy MySQL and support queries with other tools outside of Clarion/NT and is this an advantage?
• How easy is it to take an existing small TPS application and convert it to MySQL?
• Is it the case that MySQL for apps development and deployment on a Web server is free?
• Would SQLAnywhere be as good a choice as MySQL (or any other equivalent)?

Thanks

Keith

[rainerwallenius]

Hi Keith,

I have not changed my database to (any)SQL. Partly because the SQL-backend needs a completely different approach and we have no method of controlling what the end users do with the database. I have tested changing all tables to MSSQL and the software runs nicely "out of the box" - no changes needed. (of course there are some nonos like testing for eof but that is not good practice anyway...). I do have some custoers running SQL-versions of the software but that is for "company policy" -reasons.

Support and control of data is the reason I have chosen to stick with tps. And none of us is a SQL guru either so that clearly is a factor 

We have a customer that manage over 3000 customers payroll. Every customers data is in a separate directory. This is a bit extreme for us but there has been no problem at all with this approach. On the contrary, separate directories have given maximium flexibility for us. Easy to copy etc. Great for support also.

Just some thoughts

:: rainer