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?
Violating SQL principles
database;sql;database design
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).