Hi Everyone,

I am playing with a db design at the moment and an idea has occurred
to me that I would like to ask for advice / ideas on. The db is for
storing product information in a 'dual heirarchy' way. What this means
is that we have a normalised design with two 'paths' to a specific
product (Category, SubCategory, Segment)(Manufacturer, Brand, Range).
Each 'Product' has a unique id (the EAN code) as well as a unique
technical key (autonumber), and has two foreign keys (range_id and
segment_id). There are some standard fields that are applied to all
products, such as Name, Weight, Height, Width, Depth, and so on. There
are also different fields that would be great to be able to apply to
certain categories of product, for example food products would benefit
from having a 'Flavour' field, while books would benefit from an
'Author' and 'ISBN' field.

What I was thinking of doing was to have a user defined list of
appropriate fields and their data types, per category, so that
appropriate information can be stored for each without having to build
separate data tables per category. I have not tried something like
this before and was wondering if anyone has given it a go?

My initial thoughts run along the lines of having a table related to
the category table where the field list would live. What I cannot seem
to figure out is how to use SQL to return a list of products, with the
user defined fields (and their values of course), for a given category
so that each user defined field 'has a column of its own' so to speak.
I think this should be possible but I just have no luck in wrapping my
mind around a method to achieve this.

Any advice or ideas would be greatly appreciated.

The Frog

On Dec 9, 2:27*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
so you have something like

tblRange
rangeId (pk)
...


tblSegment
segmentId (pk)
...

tblProduct
productId (pk)
ean
rangeId (fk)
segmentId (fk)

now you need to introduce 'features'

tblRangeFeature
featureId (pk)
rangeId (pk)
feature

tblSegmentFeature
featureId (pk)
segmentId (fk)
feature

tblProductRangeFeature
prfId (pk)
productId (fk)
rangeId (fk)
featureId (fk)
featureValue

tblProductSegmentFeature
psfId (pk)
productId (fk)
segmentId (fk)
featureId (fk)
featureValue

On the right track Roger, but I was thinking a little simpler:

tblRange:
-range_id (pk)
-range

tblSegment:
-segment_id (pk)
-segment

tblProduct:
-product_id (pk)
-ean (unique)
-product
-range_id (fk)
-segment_id (fk)

tblFeature:
-feature_id (pk)
-category_id (fk) (this is two levels 'above' range in the design)
-feature

In theory we can list all sorts of things here. My concern is in two
parts:
1) How to effectively control the set of features that are available
to an individual product without cascading a compound primary key down
the line (so to speak).
2) How to provide a query that for any given category we would see a
list of products with their 'heirarchical' fields as well as including
- column by column - all the defined features (ie/ each feature as a
separate field in the query).

I need to be (if I am going to do this) provide the output query as
pure SQL. It cannot be dependant on any VBA to work as the database
must 'play nicely' with some of our other systems. If I have to carry
a compound key then so be it, then use a joining table to simulate a
many-many relationship.

Does that clarify what I am trying to achieve a little better? I
apologise for any lack of clarity in my earlier post.

Cheers

The Frog

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote


An entry I read recently in Tony's Microsoft Access Blog comes to mind
....

http://msmvps.com/blogs/access/archi...bad-carma.aspx


WOW! If you don't read Bad CaRMa to the end and don't say wow (or
worse) then please unsubscribe from my blog. <smile>

I followed a link from fellow MVP Mitch Wheat's blog entry When Design
Goes Bad, and The 'One table to Rule Them All!'


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Dec 9, 6:17*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
1)

tblFeature gives you a list of possible features for a product
ie. metadata
you could add fields like sequencing, mandatory flags, data type,
default value, etc


now you need a table, tblProductFeature, to assign values
to the features for a given product, ie. the feature data

tblProductFeature:
-pf_id (pk)
-product_id (fk)
-feature_id (fk)
-feature_value

2)
this is a hard one without VBA,
if it was a small set of known features, you could a query like this
pseudo query

select a.productId
, a.product
, b.featureValue as width
, c.featureValue as height
from tblProduct as a left join
tblProductFeature as b on a.productId = b.productId
and b.featureId = 1 left join
tblProductFeature as c on a.productId = c.productId
and c.featureId = 2


as you can see, you need a 'tblProductFeature' reference for every
feature
maybe you can have one query per category (assuming each category has
a
small set of features)

Roger,

Thankyou for the SQL approach, it is exactly what i was looking for.
What I will do is add code to the form that allows users to 'add a
feature' to a category, and use VBA to generate the required SQL and
save it as a query specific to each category. Depending on the number
of records and features it might pay me to dump the results to a temp
table. Either as a query or a temp table the 'play nice with other
applications' part is taken care of.

Clif, thats an extremely funny article on developing the 'Vision'
application. I cant believe that anyone would be that stupid!
Apparently ego and ambition outweighed ability and common sense.
Sounds similar to my office........ Wait! Did I say that out loud?????

Thanks guys.

The Frog

On Dec 10, 9:08*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
Hi The Frog,

If a little bit of VBA is acceptable, you could make a csv-output,
readable with Excel, and (probably) 'other applications'.

Imb

Hi imb,

Thanks for the thought. I am guessing that the IT dept here wants to
be able to get at the data without having to write any complex SQL
themselves. If I can get the userform to craft the needed SQL and save
it as a query in the back end file, then I am hoping that the IT dept
can then access it any way they wish. Basically they dont want to play
with a front end app at all and just want access to the data that is
going to be the product of a lot of user input.

The bigger picture is that the whole excercise is a data cleaning
process to produce a table of products that will have 'trusted'
definitions for their fields as well as the content of those fields.
Each row of data, and each field in those rows needs to be manually
approved. This is a big job indeed for the product lists we have, so I
am trying to ease the pain with a little application that can take the
source 'rough' data in, park it in temp tables, allow the users to
process the data, and place the result into a structured back end that
still leaves them some flexibility to alter things as the business
changes and grows. It will eventually move to a master data system and
the process will be discarded, but not until the existing lists are
processed and cleaned to a trustworthy state. It is proving to be a
surprisingly tedious and frustrating problem to solve, but by no means
impossible.

I really appreciate the feedback.

Cheers

The Frog

On Dec 10, 4:55*pm, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
Hi The Frog,

With the explanation in your answer I can understand that an output to
a csv-file is to simple. As far I understand now is that you want to
update (changed) data in the database, so in fact your form is
"connected" to the data-tables.

If this is the case, then your form must handle ànd real data ànd meta
data (fieldnames, datatypes, etc).
I have a feeling that this is possible. In fact for all continuous
forms that I have in my applications, I use the same form. This form
contains no knowledge of real data or meta data, that is only done at
opening the form, and is very dependant on the specific environment of
that form on that moment.

So, if I have a better idea of how your application (or is it still an
idea?) runs, perhaps I can give you some hints how I solved such a
problem.

Imb.

On Thu, 9 Dec 2010 01:27:58 -0800 (PST), The Frog
<mr.frog.to.you (AT) googlemail (DOT) com> wrote:

I'd sure be tempted to have lots of empty fields in the Item table.
With a field on the category table stating what type of fields should
be displayed. Then create subforms and subreports on the type of
fields. Then make visible the appropriate subforms and subreports
depending on the category type.

Yes, this is the standard boring approach. But when you consider
you've got to take into account field widths and order on the subforms
and subreports, along with lots and lots of extra code and logic,
boring is good.

If the above is making sense.

How many different sets of subforms and subreports do you anticipate?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/