Violating SQL principles

Question

I have to write a decent size database, 1GB more or less.

I have only taken an introductory semester regarding SQL databases. During the course we learned that the relational model under SQL has no implied order and that therefore when querying we should always have a variable that sets the correspondant order in the table.

However, when doing practical work I find 'convenient' to make already ordered small, infinite number of tables.

For example, lets say that you have 5 clients and that their info will never join their data together. Is it admissible to have a potentially infinite number of tables with a table per client?

For example, I append new rows already ordered to the database. The database is already ordered. Is it admissible to make an unordered query and just assume that is implicitly ordered?

All this violates the principles that I know. My boss, who does not know about databases says that if it works it works and that speed is important. What shall answer to him?

Answer 1

5 clients and that their info will never join their data together. Is it admissible to have a potentially infinite number of tables with a table per client?

I'm not sure where you're going with this. Do you have multiple copies of your Client table? do you have multiple copies of the tables that are joined with Client? Either way, both of these designs will cause problems in the future, mostly with long-term maintenance issues.

Usually what happens is that the data model changes, and then you have to propagate the change to all copied tables and hope that nothing breaks. For example, removing/modifying columns might not break referential integrity for most clients, but it might for some, so your test environment must include samples of all copied data.

Another issue is that if you're doing reporting, you need to join all the copied tables if you want your report to cover more than one subset of the copied structures. This can make for very long and unwieldy queries, and if you forget one client's table then your results will be wrong.

And then you have the problem of having queries and code bound to the names of copied tables even though they may all be doing the same thing.

There are some cases where doing this is useful and possibly necessary, but I find it's something that needs a bit of thought and a good reason to do it.

Is it admissible to make an unordered query and just assume that is implicitly ordered?

If the ordering of the result set doesn't matter, or if you plan to re-order it in your application later (maybe after some very specific in-application filtering/procesing), then I don't see why this is a problem. If you want to ensure that the result set is ordered correctly, add an ORDER BY clause to your query.

Of course, if you find that some tables are frequently hit with the same query and ordering conditions, it might make sense to order the index that your query uses so that it's ordered it the most commonly used way. This could give you some performance improvements. Even if you do this, I'd say it's still a good idea to include the ORDER BY. Mostly so that someone who's unfamiliar with the code knows exactly what the order is and doesn't have to go digging through database definitions to figure it out. How you do this may vary depending on which database product you're using (if it's even supported at all, but I think most major database engines would support this).

Answer 2

Ok - you cannot make an ordered small table - the order you insert records is not guaranteed to be the order you get them out when doing a SELECT - assuming anything else and you're going to be asking for bugs in the future even if it works now.

One table per client is fine but probably unnecessary - it's one of these things that depends on your specific situation.

However, adding an ordering clause to a query when appropriate will not be expensive - the things you need to make sure you're doing well:

Normalisation Query Structure / only retrieving the correct data

The key issue with most new database developers are that they have enormous god tables with hundreds of columns and they don't actually query for what they want - aim to retrieve the least amount of data necessary and then the ordering becomes trivial.

Answer 3

It's never guaranteed that the query will be returned in clustered index order which is the only order you're likely to get implicitly. The 'convenience' to you is that the system is behaving the way you expect it to, but someone else querying or modifying the system may have different expectations resulting in bugs.

Having the same table repeated per client is one way of doing multi-tenancy which is a separate topic.

Answer 4

One shouldn't worry about whether or not the data in the table is ordered in SQL. There's the ORDER BY clause in SQL if you need to order the result of the query. If you don't need to order resuts, just select. Performance will depend on use of proper indexing and keeping the indexes and statistics and up to date. 1 gig may seem big, but in today's world should not be that big of deal to create a performant table structure.

It is not necesary to have lots of small tables for each client. You don't want to support a database with lots of tables where just a few will be needed, unless you want to provide those levels of isolation as a service. In other words, one model that can be deployed across 1 to many instances and 1 to many servers.

There are roughly 3 levels for for your model:

• Shared - One Server, One database, Many Client, clients shared the same model and tables.
• Instance - One Server, Multiple databases, Many Clients, clients share or have thier own instance of the model.
• Server - Mulitple Servers, Multiple database, Multiple Instance, Many Clients - Clients share, have thier own instance, or have thier own instance and server (isolated)

Cost wise $ Share < $$$ Intannce < $$$$$ Server and should be priced as such.

What do you say to your boss?

We will develop 1 database model that can be deployed as needed depending on how the client wants to isolate thier data.

Answer 5

I think it is always important to have enough humility to say that I'm in over my head and get professional help.