When do you know that you've made a design flaw in a SQL/relational database?

Question

I've been working with a a relational model for a work project over the last few months, and I've gotten to the point where I'm wondering if I've made a mistake in my model's design or implemented things incorrectly. I can't go into specifics about the model because it's for work, but I'll try my best to elaborate.

The database contains data from three separate sources that are time sensitive. I.e., all of the date starts out with a DATE or DATETIME of some form. I've written a process to aggregate that data to unique values over date ranges and then run those unique values through an optimization engine in order to gain insights about an "aggregated optimal view" of the data. The process seems to work fine when there are relatively small amount of data (think ~170,000 records per date range that I select) but doesn't seem to be scaling upwards at all. Some of the queries that I've written are now taking much longer than they used to, and I think it's because of the way that I've designed the system.

Here are some of the "symptoms" of my database:

• I use indexes in my tables, but haven't defined foreign key relationships
  • I was under the impression that have well defined indexes for each of my JOINs was sufficient
• There are some queries where I have as many as 4 or 5 nested subqueries
• Some of my queries JOIN 6 or 7 tables together in order to get data
• A couple of queries that I have are >300 LOC

I could add a few more, but I think that this gets my idea across.

My question is: when does one know that their database isn't modeled properly?

Is it when your queries stop scaling? When you have to write long queries to get the data that you're looking for?

I can give more information about the stack and machine that I'm using if it helps.

Thanks

Answer 1

Modeling and performance are related but not quite the same thing. Performance and scalability will have a lot to do with what DBMS you are actually using.

Queries which seem to be slow and difficult to execute against MySQL might fly when run against Postgres for instance. This has everything to do with how intelligent the query planner is. Older versions of MySQL for instance only seem to know how to perform one kind of join: Nested-Loop. This will be fast for small amounts of data and simple queries, but can degrade quickly for complex queries.

You may not have a design flaw. You may have simply hit the performance wall with your current DBMS. Which database are you using?

In my opinion, an error in the model exists when you have insert, update, or delete anomalies in the data. Imagine you store a customer name on each invoice and the customer changes their name. To perform this update should not require updating the history of every order the customer has ever made. If it does, you've got a problem with your model.

Answer 2

Performance combines a lot of things: physical database design, yes, but also hardware resources and DBMS implementation. You want to look at how your query optimizer is dealing with your slow-running queries, and what the DBMS reports about its resource constraints, things like disk I/O queues. You might need new indexes for your search (not necessarily join) criteria. And you might well need more RAM. RAM is essential for sorting and caching, two things DBMSs do a lot. Your DBMS may also keep statistics on distributions in indexes that may need to be updated administratively.

Table count is not a measure of design quality. (If anything, more tables usually indicate a better job of normalization.) 8-table joins aren't terrible, but DBMSs vary in their ability to winnow the query plan successfully. 300-line queries are unusual; I've written many thousands of queries (in a similar problem domain) and only a handful in that vicinity. You'll want to look those over for opportunities to simplify them, particularly to remove redundant constructions. Depending on your DBMS, they might benefit from using temporary tables or materialized views.

If you find yourself re-writing the same join frequently or your tables are in some way cumbersome, that's what views were made for. There seems to be a rumor that views are slow, but that's really just a special case of anything being slow if used naively.

Sometimes it is beneficial to create your own "downstream" tables for subsequent processing: Create your view, then a table of the same design, and insert the rows from the view into the table, where you can index them. I used to name my views starting with "v", and derived tables with "tv" to distinguish them from the "real" tables. Any "tv" table could be dropped and re-created at will from its source view.

So I'm not betting your design per se is an issue, certainly not based on anything you've said, least of all performance concerns. You might need a bigger machine; think in terms of all the data for your big queries fitting in RAM. You definitely want to explore and exploit your DBMS features, and get comfortable diagnosing performance-related reports, especially query plans. Probably you need more indexes, but take care: don't sprinkle them like they were performance pixie dust. Learn how your system uses them, and add them sparingly, when you see a measurable difference.

That's a bunch of work, to be sure. But you might look at it this way: it's the underlying system that's under indictment. You don't have to redesign your tables; you just have to tend to the system that manages them.