-
We have a table TIMED_EVENTS_QUEUE, that stores events and times (like a job schedule). Some of these events will be repeating, (e.g. every day, or just ran 10 times). At the moment this is stored in one table, eg.
TIMED_EVENTS_QUEUE:
EVENT_ID
USER_ID
EVENT_DESC
EVENT_TIME
REPEAT_ON
REPEAT_INTERVAL
NEXT_EVENT_TIME
STATUS
Now a colleague wants to change this into two tables for performance purposes, e.g.
TIMED_EVENTS_QUEUE: REPEATING_EVENTS:
EVENT_ID EVENT_ID
USER_ID USER_ID
EVENT_DESC INITIAL_EVENT_TIME
EVENT_TIME NEXT_EVENT_TIME
REPEAT_ON INTERVAL
STATUS — a 1-0 relationship
My query is, if it is a 1-0 relationship and these two tables are always joined when quering or updating, the extra table will be futile, more maintenance (within the application), and use more space. Am I correct, or does anyone see the need for this extra table? Thanks.
-
From the information you have given, it looks like you need to add EVENT_TYPE column and specify if it is time_events or repeating_events and use ONLY ONE table.
Hope this helps.
- AD
-
Splitting a single table vertically into 2 tables (A,B) with a 1:0,1 relationship is only beneficial in 2 scenarios:
Scenario 1:
- You have a table with a number of densely-filled columns and a number of rather sparsely-filled columns.
- These sparsely-filled columns take up a sizeable amount of room in the row.
- These sparsely-filled columns are modified and selected much less than the densely-filled columns.
Basically, the tradeoff is:
- You are splitting the columns off into another table to save on space in the main table. This will make tablescans faster, allow more of your table in the cache, etc. This benefit is usually not terribly large.
- When you *do* need this information, you will need to join to this other table. Additionally, *most* DML will will need to occur on 2 tables. This overhead can be substantial.
Therefore, you had better be saving a *lot* of space and touching that split-off data very rarely to see real benefit from this
Scenario 2:
- You have a large table with a lot of information that can be densely-packed, but you generally don't need.
- There are some specific columns that are only filled for the records you *do* care about.
For example, you have a database with some huge amount of what we'll call 'leads', or people to cold-call for sales. This data may be populated in some automated fashion. When you actually get somebody on the phone who is interested (in whatever), you get a lot more information on that person. Since this information is *still* pertaining to the lead, you might have originally designed these as columns in the lead table. However, if you split these columns off into another table, you will have a *much* smaller table for your 'customers', which are more important than general leads
In this case, the benefit is easier to obtain. If most of your DML only deals with those new, 'customer' fields, then those statements will run much faster. But retrieving that 'general' information for the customer (name, etc:) ) will be *much* slower.
Of course, what *should* be done in this case is to simply duplicate all the columns from 'lead' into 'customer' and thereafter disregard or delete the 'lead' data for that customer.
------------------------
So, given all that, the general answer is that it is not a good idea. You have to meet some pretty steep criteria for it to make sense, IMHO.
- Chris
