Development Quandary...

[Ocean West]

Have a solution i am working on that the client has attributes values for equipment items.

At present there are about a 22 or more attribute items for each equipment each field can have it’s own value list such as:

Power Requirements

Power Cord Receptacle

Dedicated Circuit

Emergency Power

Dedicated Circuit and Emergency Power

or

Data Network

Cat 5e

Cat 5a

Cat 7

Wireless WiFi Required

Single Data Outlet Required

Multiple Data Outlets Required

VoIP Device

To Central Monitoring Station

Some fields request user entered value like:

Height [cm], or Weight [kg]

I will have some other technique using field triggers to make sure that values

entered in either metric or english units will auto convert.

As it stands these are 22 independent fields. We don’t print columnar reports or search on these attributes, other then just a product profile which something that can be easily created if desired.

In my mind I am toying with the idea of having a dedicate value list table that will

facilitate a 2 level hierarchy for each attribute.

Then when working within the catalog a portal that will add the default attribute records for each equipment item.

This facilitates the ability to allow the end user to ADD additional attribute points and values to the solution with out having to adjust schema, and layouts just to fit a new field, and hook up a value list.

However the recorded Attributes will grow exponentially if I had 5000 equipment items that could account for over 110,000 data points - many of which may be blank.

This would allow the item to include multiple values for an attribute such as there could be two records for Data Network (in my example above) should the item have more than just one value.

My other thought was if perhaps the data for each record could be just stored in the equipment table in a single field with the necessary meta tags and record id’s when the user edits a record I could explode the field into a temporary mechanism for editing or viewing the data. Thus if there are any importing & exporting of records all the data is self-contained.

Your ideas are appreciated.

Thanks

Stephen

Edited May 22, 2010 by Guest

[comment]

I don't quite follow this part:

Then when working within the catalog a portal that will add the default attribute records for each equipment item.

However the recorded Attributes will grow exponentially if I had 5000 equipment items that could account for over 110,000 data points - many of which may be blank.

Why not add the attributes as and when you need them - i.e. when you have a value to assign?

[Søren Dyhr]

I came to think of this:

http://www.spf-15.com/fmExamples/BilloM.fp7.zip

--sd

[RodSierra]

Is it possible to break these 22 down into a hierarchy, or are they random across each top level entity? With a hierarchy, life is much easier.

[Ocean West]

@comment yes that was my thought was to make a mechanism for picking the possible attribute that is needed for the current item and perhaps in the resource table indicate if that attribute can be added more then once.

Then when the user chooses items it would auto reduce based on active current selection.

the result in the child table will be far fewer null records. I suppose in my example if EVERY item had all 22 or more attributes assigned then it would be a lot of records - not that that bothers me in any sense.

Just exploring the idea of keeping the data self contained. Where as the user interface would store explode the data into a temporary structure like VirtualLists.

@rod, I'd use it in a hierarchical nature only to make the database easier to work with. I could have an Attributes table and an Value Table an attribute can have one or many values. But a single hierarchy type table is stuff I have built many times, seemed like a good fit in this example.

Your both right the attributes are generic in nature as needed: because for example there would be NO need for any reference to which type of Medical Gas requirement is needed for a Trash Receptacle or Office Telephone.

I suppose there could be an argument of a third level of the hierarchy where the type of equipment could call a different subset of attributes but at the present the deciding variable for that decision is absent from the equipment list.

Edited May 23, 2010 by Guest

[comment]

Well, you have somewhat conflicting requirements for the user interface: on one hand, you want a "grid" arrangement - i.e. show the user all possible attributes for the given object, to fill out as many of these as they feel necessary. OTOH, the user may need more than one value for the same attribute.

I am not even sure how that should look, before considering what would be needed to build it. The only comparable UI that I can think of is Apple's Address Book, where you can click a ⊕ to add another value in the same category.

[David Jondreau]

I like using individual tables for value lists of complex custom values, rather than one biggie with "value list names." I have no problem with 30 value list tables.

Partly because it's simpler. I could build a hierarchy, but what if someone inherits the db and doesn't understand it?

But it's performance mostly. I've had issues with filtered value lists being used in pop ups for displaying the "secondary" field. The first time the layout loaded it would take 30 seconds on a hosted solution.

Edited May 24, 2010 by Guest
Addressed to OW, not comment.

[Ocean West]

no need of a any grid.

Attribute Value pair

SOME ITEM EQUIPMENT

MFG XYZ123

Power = 120v

Network = Cat 6e

Height = 23in

or in the case of multiple req it would just show

Network = Cat 6e

Newtork = VoIP Device

My thought was just a portal to store data nothing fancy.

[comment]

no need of a any grid.

OK, then I guess I don't understand the issue. What is missing from the attached file in order to meet the requirements?

Attrib.zip