Items with some, but not all, fields in common

[estoyer]

Greetings all,

I've been asked to create a database to track and report testing progress of a large number of IO points for an industrial plant. I can't seem to come up with an elegant way to split up the data, so I'd appreciate input from folks with more experience.

The IO points come in five "flavors" (sorry I can't go into detail; NDA issues). Every IO point has a certain number of fields in common -- port, channel, things like that. Additionally, each flavor has its own attributes which are irrelevant for all the others. So type A has port, channel, and foo; type B has port, channel, and bar.

They're all going into the same report, and I'd like to be able to sort just by port and channel, ignoring flavor. So on one hand, cramming everything into one table with fields for port, channel, foo, and bar, and then just leaving 9/10ths of the 'bar' values blank, is appealing. It would be easy to add new IO points and maintain unique port/channel combinations.

The downside is that there are so many flavor-specific fields that if I display a blank space in the report for every field that doesn't apply to a given flavor, it's going to get very crowded and very ugly. I might be able to hack something together in VB to hide the irrelevant fields, but it seems like that would be difficult to edit and maintain.

One alternative would be to put each flavor into its own table, and then have a report that pulls data from all 5. It seems like the queries involved would get messy and I'm not sure how much freedom I would have with sorting/displaying the final list of items. I'm not wild about having to use subforms for data entry either, but it's doable.

I've also considered putting the core data (port/channel) into one table, and then having five tables for the details -- one that only contains foo, one that only contains bar, etc. But in my extremely limited experience, this seems like a pretty unintuitive way to organize a database.

From a design/standards perspective, which would be the least-worst option? I'm leaving this project right around the time this database will start to see heavy use, so I want to make things as straightforward as possible for my successor.

Thanks.

[blater]

I wouldn't recommend seperate tables for each of the flavor types. Here are two possible methods....

1) Your first choice - put then into one table. This is relationally correct as the flavor-specific attributes have the flavor as the determinant (i.e they really do belong to the flavor).
i.e

ioPoint
--------
ioPointID
flavorID

ioPointFlavor
-------------
flavorID
port
channel
foo
bar
baz
:
:
etc

If there are a very large number of these attributes you could do the following instead:

ioPoint
--------
ioPointID
flavorID

ioPointFlavor
-------------
flavorID
port
channel


ioPointFlavorAttributes
--------------------------
flavorID
attributeType  string
attributeValue string

Here a list of zero or more attributes and values is associated with each ioPointFlavor.
Depending on your requirements this may make your reporting easier, although maintenance of the will require more programming.
It would make it easy to add new types of flavor attribute. Note you'd probably want a flavor attributeType lookup table as well.

[estoyer]

Thanks for the quick reply!

I'm a little puzzled by your response, though, and think I may not have been clear. All IO points of type A will have a value for foo, but these will be different values. No IO points of type B will ever have a value for foo. I'm just using "flavor" to refer to the kinds of data that will be relevant to a given IO point, not what that data will actually be.

That being the case, I can't think of a reason to split (in your first example) ioPoint and ioPointFlavor, as it would be a one-to-one relationship, correct?

I'm still learning the terminology as I go along, sorry for any confusion.

[amigura]

could you list db fields without real names

[estoyer]

I don't have the docs in front of me now anyway, so nonsense-fields it is.

IO1:
PartType
Node
Port
Channel
Speed
Direction

IO2:
PartType
Node
Port
Channel
Height
Length
Width

IO3:
PartType
Node
Port
Channel
Speed
Height
Mass
Volume

etc... On average there are about a dozen fields unique to a given IO type, and half a dozen that are shared with one other IO type.

There are multiple (in some cases four or five, in some cases a few thousand) IO points of each type. All IO points, regardless of type, need to go into the same report, sorted by node/port/channel.

At this point I'm leaning toward putting them all in the same table and using conditional formatting to hide irrelevant fields in the report. The report's going to be a nightmare to maintain no matter what I do, anyway.

[amigura]

you talk about blank fields, weell that would depend on what u want in your report. if you want table1 in report then there are no blanks. but if you want table2 then there will be blanks as there will be no data for some fields. if you just want to dump attributes into columns regardless like

PartType - Node - Port - Channel - atrribute 1 - atrribute 2 - arribute 3

in the way of php i would do something like this.

$attrib=array();

get db results

// if atrribute is not blank set atrribute array
if(!empty($Speed)){$arrib[]=Speed-value;}
if(!empty($Direction)){$arrib[]=Direction-value;}
if(!empty($Height)){$arrib[]=Height-value;}
ect.....

so if speed is not empty speed value would be $arrib[0]

PartType - Node - Port - Channel - arrib[0] - arrib[1] - arrib[2]

tables setup

table1 - part type id
PartType
Node
Port
Channel

table2 - part type attributes
PartType
Speed
Direction
Height
Length
Width
Height
Mass
Volume

[estoyer]

Every field relevant to a given IO type must visible in the report. This is going to be our checklist when we're running tests at the construction site.

Say TAG1234 is a type 1 IO point. That means in our checklist, the entry for TAG1234 needs to display speed and direction, but not height, mass, volume, etc.

The IO type is what's relevant, not whether there happens to be a value in that particular field. We're still waiting for information from the vendor on some things, and others will be measured onsite.

Anyway, all of this is kind of off the subject. I can think of several different ways to implement this; I was hoping to find out if there is any one standard approach for data like this, to make my implementation more intuitive for the next person.

[amigura]

approach it as a shopping cart. product data - optional data - category

sound like you would be better of doing two reports. one for speed and one for size.

db results for cat1
db results for cat2

or

db results order by port,channel,cat