The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Design a DB for both single and multiple user environment


The program currently use xml as document format and I'm going to switch to SQLITE database. the program might be extended to client/server architecture in the future, so my concern is how to design the database so it's schema is ready for multiple user environment, and how to design/code so that in the future it can be easily extended to a C/S system.

My Initial thoughts:

on the DB
a) Each record to have a UID as the primary key.

on coding:
a) have the data access layer separated.
b) when writing the data access functions always keep in mind that the code must be able to moved the server side.

Do you guys have other advises? Thank you.
Saturday, November 22, 2008
I didn't see you mention database transactions.
They're very important in a multi-user environment.
Saturday, November 22, 2008
The answer to your question is simple: don't use SQLite.  I know almost nothing about it, but a quick read of its web site this morning told me everything necessary:  it is serverless, which means there is no control process on the database server to keep multiple-user apps from borking the database.

So-called "file system" databases are notorious for corrupting data.

This will touch off a war, but: if you are considering multi-user deployments of your application, do yourself a favor and rewrite using a true client/server database with a control application on the database server - don't use a file-server database such as SQLite.

And don't fall into the trap of "but SQL Server Express is a HUUUUUUUUUUUGE DOWNLOAD!"  It's only downloaded once.  And I'm not necessarily advocating SSE (there are lots of true client/server databases and most work great) - I'm just saying that the download size, or setup, or virtually any of the other complaints you'll hear is not relevant to the business problem you are solving by using a robust client/server database.
Karl Perry Send private email
Saturday, November 22, 2008
I use the Firebird database - it's available in embedded or server version.  My applications have a database connection dialog that allows the user to select from a local (file system) database or a remote (running the firebird server) database.

The physical database file can be copied between the two systems using simple xcopy.

In this way, you can write the code once and use it for both standalone and client-server configuration rather than writing for xml and then writing for standalone db and then writing for client-server...
B2B MicroISV
Saturday, November 22, 2008
"My Initial thoughts:"

There's a lot more to it than what the original poster is suggesting.

However, it's worth pointing out that in a well designed database, there should be no difference between a single user and hundreds of concurrent users. The concept of "eventually moving to a client/server architecture," is meaningless.

This early in the process, you should be paying more attention to normalization and understanding when to de-normalize, than to whether the database is stored in a flat file or on a separate server. You should be thinking about whether your logical model is optimized for a lot of "reads" or "writes." You'll also want to give some thought to security; are you going to protect data on a per table basis, or via the abstraction layer?

Ideally, you wouldn't issue the SQL commands (for creating the database) directly to the database, but instead write scripts and execute those. If you write the scripts well, you can simply run the same scripts against SQLite and SQL Server 2008.

Unfortunately, there's just too much good advice to quote in a forum post. A recent book that I've read, that I like, is Eric Johnson and Joshua Jones' "A Developer's Guide to Data Modeling for SQL Server". Despite the title, the vast majority of the book is actually generic and applicable to most databases out there.
Saturday, November 22, 2008
> there should be no difference between a single user and hundreds of concurrent users <

While true enough, there can be a difference between a single connection and many even for a single user scenario.  Some file databases can be opened exclusively, bypassing concurrency locking.  This can make a dramatic difference in performance in some situations.

If the OP is considering scaling to multiple users it is an irrelevant point though, I admit.
Saturday, November 22, 2008
Thank you all for your comments.

I choose sqlite because it's lightweight, fast and most importantly supports full text search. it will be serving as the document format for the current version of the application. While extending to C/S architecture is possible but it's not the priority, anyway I still want to make the database design and coding be as much extensible as possible, so I posted my question.

Maybe I should have said it will be a multi-tier system rather than C/S system, because there will be definitely a server software on the server between the client and the database.
Sunday, November 23, 2008

"Maybe I should have said it will be a multi-tier system rather than C/S system, because there will be definitely a server software on the server between the client and the database."

Would you like to expand on this statement a bit so we know what you are talking about?  Does not make sense as described.
ps Send private email
Sunday, November 23, 2008

Thanks for asking. Actually currently it's a program like Visio, and in the future there is a possibility to extend it to allow collaboration, to achieve that we'll need to develop a server side software, and there will be a back end database.
Monday, November 24, 2008
What's wrong with MySQL? It is not expensive commercially, and under appropriate open source options .. free.. and you can find a remote db hosting option for next to nothing monthly costs.
Monday, November 24, 2008
Hi Ajax,

I did say anything about my sql :)
Tuesday, November 25, 2008
Postgresql does a better job than MySQL
Saturday, November 29, 2008
* Use always a SINGLE field for a primary key, AVOID using composite keys, are difficult to use, even in detail tables, many DB managers doesn't allow them. AS an example, try to do a join between 2 tables with a single primary field, and a join query with composite keys...

* Don't use autoincremental keys, always something that can be written on an SQL insert script, UIDS are fine.

* prefix field name with table's name or abbr. as standard,  example: customer_code, sale_code, product_name, cust_name. You may have to write more letters, but may avoid conflict names an use of alias...

* Multiple user databases usually have an additional user table, sessions table. Other tables may store an additional  user or session foreign key field, but usually most tables stay the same.

mrk Send private email
Monday, December 08, 2008

Tuesday, December 16, 2008

Tuesday, December 16, 2008

Tuesday, December 16, 2008

Tuesday, December 16, 2008

Tuesday, December 16, 2008

Tuesday, December 16, 2008

Tuesday, December 16, 2008

Wednesday, December 17, 2008

Wednesday, December 17, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz