Combining database tables

Question

Hi all,

I have two tables which look kind of similar and i was thinking about combining them and thought i would get some input from everyone. Here's what they currently look like:

Issues

Id  | IssueCategory   | IssueType | Status | etc..
-------------------------------------------------
123 | Copier          | Broken     | Open   |
124 | Hardware        | Missing    | Open   |

CopierIssueDetails

Id | IssueId | SerialNumber | Make | Model    | TonerNumber | LastCount
---------------------------------------------------------------------
1  | 123     | W12134       | Dell | X1234    | 12344555    | 500120

HardwareIssueDetails

Id | IssueId | EquipmentNumber | Make | Model | Location  | Toner | Monitor | Mouse 
-----------------------------------------------------------------------------------
1  | 124     | X1123113        | Dell | XXXX  | 1st floor | 0     | 1       | 0

What do you guys think about combining these two tables into one. Would it be a good idea or is it better to keep them separated like this? Thanks in advance for any suggestions.

Answer 1

Ultimately the question boils down to "Is this a one-to-one or one-to-many ratio?" If there is ever the possibility to have two or more items in a given table related to the items in another table, keep them separate. If not, then combining them would simplify your queries.

Answer 2

You certainly could, though you may want to consider making the columns slightly more generic so that you don't have a number of empty columns. For example, you know that Copier's don't have monitors or mice, so it makes little sense for a copier row to have a monitor/mouse column. You could make those columns more generic, by changing it to one column called "HardwareType" with the values:

0 = Monitor,

1 = Mouse,

2 = Monitor & Mouse,

3 = Copier

(You could then put this in a look up table, to keep track of your IDs).

The things you want to consider are:

1) While combining these tables increase speed of the application?

2) While combining these tables increase maintenance of the application?

If the tables are both very large, and used for different tasks, it may not be wise. For tables that are "roughly the same" in terms of concept, then it's not bad to combine them as long as it doesn't impact performance, and as long as you avoid a lot of unnecessary columns that are only specific to certain items but not others.

Answer 3

Well, it depends on how big you expect this complexity to get in future.

If its just going to be few more issue types differing with few specific fields and having many common fields, it make sense to combine all of them into one and leave the fields null that do not apply in a particular case.

Other approach could be to combine all common fields into one table, and add another common additional details table with structure like:

IssueID
FieldName
FieldValue_Text
FieldValue_Number
FieldValue_Float

and may be a master table for additional field names with columns like:

FieldID
FieldName
DataType

In the end, I'd say it the CONTEXT that would justify any design and its difficult to assert your question without looking at the complete picture.

Answer 4

I would need a compelling reason to combine them, because they don't look the same. Maybe there is a compelling reason, but I don't see one offered.

In mechanical design, one faces the decision of whether two parts are the same enough to be called by the same part number, or are different and deserve different PNs. The rule to decide this is "Form, Fit, Function."

• Form: Are they "the same enough?"
• Fit: Are they interchangeable?
• Function: Do they perform the same purpose, meet the same need?

You can try applying this criteria to your schema.

Answer 5

Issues to consider:

1. Do the tables have different data? I strenuously avoid making overly-generic fields. In this case, "Serial Number" and "Equipment Number" sound like they may be pretty much the same thing. But "Last Count" wouldn't apply to a computer and "Monitor" and "Mouse" don't apply to any copier I know. Yes, you could let these fields be null when not applicable. If you do combine, I would strongly urge you to NOT make a field that holds the "Mouse" code for computers and the Count for copiers. That way madness lies. But you could include both sets of fields and leave them null when not applicable.

2. Are the tables used differently? If you have one set of things that you do or anticipate doing that only work on hardware issues, and another set of things that only work on copier issues, and little or nothing that works on both, that's a good reason to keep them separate. If there are many operations that process both, and you are continually writing UNIONs on the two tables, that's a sign that they really should be one table. While I believe that in principle your schema should model the real world that you are dealing with, and the code should come after the schema, in practice, if when writing your code you repeatedly see that a different schema would be easier to work with, that's a strong clue that you are NOT accurately modelling the real world that you are dealing with.

3. What future data are you likely to create? Are there six other types of "issue" that you will be dealing with, so that instead of 3 tables you will soon have 9? If so, what data will those carry? How will they be used? Or is this likely it?

I'd consider performance last. Changing your schema to improve performance should be something only done AFTER performance has proven to be an issue, or when you have done some actual benchmarks to demonstrate that it will be. There's a lot of premature performance optimization done on databases that in fact does little or nothing to improve performance in practice but that results in unnormalized and sloppy data.

Answer 6

One thing I consider in these situations is "How will these things evolve?". For example, if you need to add a new column for Copiers, what is the likelihood that Hardware will need that same column? What about reporting, do you typically have to combine the information, or do you typically have separate reports for the two types?

If the two things seem likely to evolve/be used separately, then I would recommend ignoring the fact that they look very similar; Otherwise you end up with special cases littered throughout your queries.