Breaking down a table with too many fields

[maultiper]

I have a database that tracks:

Product Type > Product Number > Serial Numbers > and Service History (for each serial number).



I am struggling with "Service History". It has some fields that would be required regardless of what is done (Date, tech etc.), but most of the fields are only applicable for a certain "type" of product / service.

I know I could just stick all those fields in the "Service History" table, but that's a huge amount of fields, most of which would be blank for any given record. So I created a different table/form for each type of "Service History Detail". Say "Car breaks", "Car exhaust", "Boat exhaust", "Lawnmower".

Can anyone give me some advice on how to connect these "Service History Detail" tables, so I ultimately can get a form that displays:

ENTER: Product Type > Product Number > Serial Numbers
RETURN: The applicable values from the "Service History Detail" table(s) that are applicable. Note for a Particular Car #123, The history could be in multiple tables (breaks, exhaust, tires, body etc.)

Any guidance would be appreciated!

[nick404]

The easiest way would be having a table with each "service history detail" as a separate field. An entry form could be a series of checkboxes or toggle boxes that get checked for what is wrong with the product. Yes it is a lot of fields that may be left blank, but I can't think of a different way to associate so many different attributes to one item and be able to re-display them when you search on the item's call number.

[maultiper]

Thanks nick404,

It's somewhat reassuring to hear this isn't super easy to fix.
Perhaps there is no elegant way to do this. In your experience, would having a huge table with a lot of inapplicable fields cause significant performance issues?

[nick404]

I don't think performance should suffer too much, at least nothing super notable if you compact & repair the db regularly.
How big are we talking, though... how many records do you think this table will contain? And how many fields will you have in this table that describe the service history?