Database design question

Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql

beginning learner, first database, planning stages

(I think the underlying question here is whether to normalize or not to
normalize this one data field - but i'm not sure)
:-)

Background info:
Part of my project involves storing data about some objects in table
"tblComponent"
One of the data items(fields) is an alphanumeric identifier("fldFeature").
The identifier is composed by using L for left R for right and then a letter
for which condition it has
to oversimplify I'll say there are 3 possible conditions, "a", "b", and "c"
so a piece might have any combination "LaRa", "LaRb", "LaRc", "LbRa", ...etc
There is also a rare possible variation for any condition which could be
applied as a numeric qualifier, 1 ... n
So it might get as complicated as "La1Ra" etc
The standard choices are finite and where I'm simplifying and saying 3 it is
acutally 11 choices(120 combinations for L and R).
The variations are theoretically "unknown" but in reality would be very rare
and very unlikely to be more than one or two variations for a given standard
condition. - to allow for unknown future variations i'm just adding this
option to make the system "open ended"
So I have kind of two things going on...a static list of 120 combinations
(which would account for the vast majority of cases) but also a possibility
of an unknown number of subtle variations which would not be static...

I'm trying to figure out the best way to store these alpha "codes"

I could just store the final code as a text field "fldFeature" in table
"tblComponent" but after reading innumerable turtorials on normalizing
databases I thought I should store the actual "legal combinations" alpha
codes(120 possible combinations) in a table, "tblFeature" and just store the
Primary key of that table, "fldFeatureID" as Foreign Key in the field
"fldFeatureID" in table "tblComponent"
but I'm not sure what advantage that would have over just storing the text
string
I'm also not sure if it's workable since the additional variations mentioned
above are also possible and wouldn't fit in the "static" list of
combinations.

for the static list I could just make up a list of all possible combinations
and store in a table
or it could be a collection property of a class object
or even a udt hard coded somewhere...

Question:
Which option is better?

If tables are the way to go, should I just make a list of all combinations
(120) (11x11 matrix)
or should I have one Table to just store two records "L" and "R" and another
table to store 11 records , "a"-"k"
and then some kind of join or union would produce the 120 variations???
but then how to get the unknown variation plugged in there which would be
user input at some stage.

as you can see I'm a bit confused on what the best "layout" is for the
design here.
thanks for any input
Mark

I would store the values separately - no question. Putting the pieces
together is trivial. But doing the reverse can be a real pain in the
arse. I think if you store them separately, you may have a wider
table, but it'll be a LOT easier to do summaries very easily. If you
don't, they'll be a nightmare.
You could always try it both ways, and see for yourself (no teacher
like experience!), but there's no way I'd put all that stuff in one
field. "Smart" keys are just a total nightmare and a basic no-no.
Consider the case where you need to generate some new report based on
that concatenated key - then you have to split all that junk out... not
a pretty scene.

Hope this helps a little.

MP wrote:[color=blue]
> Greets,
> context: vb6/ado/.mdb/jet 4.0 (no access)/sql
>
> beginning learner, first database, planning stages
>
> (I think the underlying question here is whether to normalize or not to
> normalize this one data field - but i'm not sure)
> :-)
>
> Background info:
> Part of my project involves storing data about some objects in table
> "tblComponent"
> One of the data items(fields) is an alphanumeric identifier("fldFeature").
> The identifier is composed by using L for left R for right and then a letter
> for which condition it has
> to oversimplify I'll say there are 3 possible conditions, "a", "b", and "c"
> so a piece might have any combination "LaRa", "LaRb", "LaRc", "LbRa", ...etc
> There is also a rare possible variation for any condition which could be
> applied as a numeric qualifier, 1 ... n
> So it might get as complicated as "La1Ra" etc
> The standard choices are finite and where I'm simplifying and saying 3 it is
> acutally 11 choices(120 combinations for L and R).
> The variations are theoretically "unknown" but in reality would be very rare
> and very unlikely to be more than one or two variations for a given standard
> condition. - to allow for unknown future variations i'm just adding this
> option to make the system "open ended"
> So I have kind of two things going on...a static list of 120 combinations
> (which would account for the vast majority of cases) but also a possibility
> of an unknown number of subtle variations which would not be static...
>
> I'm trying to figure out the best way to store these alpha "codes"
>
> I could just store the final code as a text field "fldFeature" in table
> "tblComponent" but after reading innumerable turtorials on normalizing
> databases I thought I should store the actual "legal combinations" alpha
> codes(120 possible combinations) in a table, "tblFeature" and just store the
> Primary key of that table, "fldFeatureID" as Foreign Key in the field
> "fldFeatureID" in table "tblComponent"
> but I'm not sure what advantage that would have over just storing the text
> string
> I'm also not sure if it's workable since the additional variations mentioned
> above are also possible and wouldn't fit in the "static" list of
> combinations.
>
> for the static list I could just make up a list of all possible combinations
> and store in a table
> or it could be a collection property of a class object
> or even a udt hard coded somewhere...
>
> Question:
> Which option is better?
>
> If tables are the way to go, should I just make a list of all combinations
> (120) (11x11 matrix)
> or should I have one Table to just store two records "L" and "R" and another
> table to store 11 records , "a"-"k"
> and then some kind of join or union would produce the 120 variations???
> but then how to get the unknown variation plugged in there which would be
> user input at some stage.
>
> as you can see I'm a bit confused on what the best "layout" is for the
> design here.
> thanks for any input
> Mark[/color]

You might want to check out non-relational databases first to see if
they offer any advantages over relational databases for your situation.
If not, many people here can help you come up with a plan to create and
handle the data relationships.

James A. Fortune
CDMAPoster@FortuneJames.com

<pietlinden@hotmail.com> wrote in message
news:1135225299.886251.230000@g49g2000cwa.googlegr oups.com...[color=blue]
> I would store the values separately - no question. Putting the pieces
> together is trivial. But doing the reverse can be a real pain in the
> arse. I think if you store them separately, you may have a wider
> table, but it'll be a LOT easier to do summaries very easily. If you
> don't, they'll be a nightmare.
> You could always try it both ways, and see for yourself (no teacher
> like experience!), but there's no way I'd put all that stuff in one
> field. "Smart" keys are just a total nightmare and a basic no-no.
> Consider the case where you need to generate some new report based on
> that concatenated key - then you have to split all that junk out... not
> a pretty scene.
>
> Hope this helps a little.[/color]

Hi Piet,
Thanks for the response.
If I may ask a clarifying question or two...<g>
[color=blue]
> I would store the values separately -[/color]

meaning you would break the alphanumeric descriptor into it's component
parts and store those in separate fields in "tblComponent"?
So instead of one field, "fldFeature" whose value would be something like
"La1Rb2" you're suggesting breaking the string into it's component parts and
creating I guess four fields...
fldLeftFeature, value: "a"
fldLeftVariation, value: "1" (or null if no variation)
fldRightFeature, value "b"
fldRightVariation, value "2" (or null if no variation)

is that the idea?

and just to clarify what you mean when you talk about keys
this field in my plan is not a key nor a part of a key but just a piece of
data about a specific object
[color=blue]
> "Smart" keys are just a total nightmare and a basic no-no.
> Consider the case where you need to generate some new report based on
> that concatenated key - then you have to split all that junk out... not
> a pretty scene.[/color]

The alpha descriptor is really one string or piece of data to me ( in my
thinking process)

so when I run a report I'm going to be looking for every piece who matches
the following criteria
There is another field, fldFamily which holds a string value such as "a"
so my query would be something like

'get all "a" family items - and get a list of their feature variations
Select fldFeature From tblComponent
Where fldFamily = "a"

'then somehow I have to sift that recordset to find the list of all distinct
features

'for simplicity I'll say there are only two possible variations in this
case, either "LaRa" or "LbRb"
'then somehow group all fldFeatures to get a collection of whatever features
exist in the recordset
'and then somehow do a ForEach on the collection of features
'so for argument sake lets say i get that list of two features in a
collection (or probably it will be a recordset)
ForEach sFeature in colFeatures
Select * from <original recordset above>
Where fldFeature = sFeature

'then I can do a count of those items somehow...
so i end up with a report like
Family"a" Feature "LaRa" count = 10
Family"a" Feature "LbRb" count = 20

so what you're saying is instead of the above scenario, do something like:
Select * from <original recordset above>
Where fldLeftFeature = "a"
And fldLeftVariation = Null
And fldRightFeature = "a"
And fldRightVariation = Null

is that the idea?

Thanks for your input
Mark

<CDMAPoster@FortuneJames.com> wrote in message
news:1135226321.406300.288380@g49g2000cwa.googlegr oups.com...[color=blue]
> MP wrote:[color=green]
> > Greets,
> > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> >
> > beginning learner, first database, planning stages
> >
> > (I think the underlying question here is whether to normalize or not to
> > normalize this one data field - but i'm not sure)
> > :-)
> >[/color][/color]
[color=blue]
> You might want to check out non-relational databases first to see if
> they offer any advantages over relational databases for your situation.[/color]

I don't know too much about non-relationals either...do you have any sites
in mind that would give an overview?
I didn't think that was a 'current' methodology anymore but I'm trying to
learn all I can about databases cause I can now see how their power could
assist with some information retrieval that I need to implement.

[color=blue]
> If not, many people here can help you come up with a plan to create and
> handle the data relationships.[/color]

This small example was just talking about one field in one table in an
overall scheme.

I tried to get some pointers on overall layout of tables and fields
previously but I think the question was too broad and complex for questions
and answers in these forums.
Thats' why I'm now trying to find teeny tiny pieces of questions to ask here
even though I really need help with the broader overview of the tables
design but it seems to big a question for these forums.

Thanks for any info you can share.
Mark

MP wrote:[color=blue]
> <CDMAPoster@FortuneJames.com> wrote in message
> news:1135226321.406300.288380@g49g2000cwa.googlegr oups.com...[color=green]
> > MP wrote:[color=darkred]
> > > Greets,
> > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> > >
> > > beginning learner, first database, planning stages
> > >
> > > (I think the underlying question here is whether to normalize or not to
> > > normalize this one data field - but i'm not sure)
> > > :-)
> > >[/color][/color]
>[color=green]
> > You might want to check out non-relational databases first to see if
> > they offer any advantages over relational databases for your situation.[/color]
>
> I don't know too much about non-relationals either...do you have any sites
> in mind that would give an overview?
> I didn't think that was a 'current' methodology anymore but I'm trying to
> learn all I can about databases cause I can now see how their power could
> assist with some information retrieval that I need to implement.[/color]

I meant post-relational. Here's a link:

http://www.intersystems.com/healthcare/healthcare.html

When I saw the possibility of components with a large relational map it
made me think that it might hold some promise.
[color=blue][color=green]
> > If not, many people here can help you come up with a plan to create and
> > handle the data relationships.[/color]
>
> This small example was just talking about one field in one table in an
> overall scheme.
>
> I tried to get some pointers on overall layout of tables and fields
> previously but I think the question was too broad and complex for questions
> and answers in these forums.
> Thats' why I'm now trying to find teeny tiny pieces of questions to ask here
> even though I really need help with the broader overview of the tables
> design but it seems to big a question for these forums.
>
> Thanks for any info you can share.
> Mark[/color]

Maybe your problem is simple. Many people in CDMA are quite good at
normalization and should be able to give lots of advice about table
structure if the problem is laid out logically.

James A. Fortune
CDMAPoster@FortuneJames.com

<CDMAPoster@FortuneJames.com> wrote in message
news:1135277208.272056.320730@z14g2000cwz.googlegr oups.com...[color=blue]
> MP wrote:[color=green]
> > <CDMAPoster@FortuneJames.com> wrote in message
> > news:1135226321.406300.288380@g49g2000cwa.googlegr oups.com...[color=darkred]
> > > MP wrote:
> > > > Greets,
> > > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> > > >
> > > > beginning learner, first database, planning stages
> > > >[/color][/color][/color]
[color=blue]
>
> I meant post-relational. Here's a link:
>
> http://www.intersystems.com/healthcare/healthcare.html[/color]

Thanks for the link I'll check that out...have not heard the term before
'Post-Relational'

[color=blue]
>
> When I saw the possibility of components with a large relational map it
> made me think that it might hold some promise.
>[color=green][color=darkred]
> > > If not, many people here can help you come up with a plan to create[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > handle the data relationships.[/color]
> >[/color][/color]

[color=blue]
> Maybe your problem is simple.[/color]

Absolutely it is!!! I can sum it up in one word. Ignorance.(mine)
:-)

Many people in CDMA are quite good at[color=blue]
> normalization and should be able to give lots of advice about table
> structure if the problem is laid out logically.[/color]

well that's what I was trying (and I guess not succeeding at very well)
I thought if I reduced the question down to one field in one table that
would be a bite sized piece appropriate to this venue.

When I tried to present the 'overall' 'schema' map to get pointers on
tables/relationships it seemed to be too big a question for this type of
forum.
That's why i've tried to back up and just ask what I thought were very
small, tightly focused, oversimplified example questions.

I've been reading hundreds of articles, posts, etc about database design/
normalization / relationships etc
but since this is all new I'm still just beginning to get a glimmer of what
my structure will really be.
I also tried to hint around at being willing to pay someone for some one on
one tutoring/mentoring in terms of the overall plan but got no nibbles on
that either.
I'm clear on what data I need to store, what questions I need to ask of it,
and what reports I need to be able to generate. I think I'm somewhat clear
on what tables will need to be created to accomodate that in a normalized
fashion, and to some extent what fields each table will need to include(at
least initially).
I'm less clear on the exact structure of every possible table and the exact
syntax of each query, in order to get those questions and answers processed.

I'd be happy to find that i was wrong and that someone wanted to hear the
actual overview and give pointers on that as well.
:-)

Thanks for your help
Mark

MP wrote:[color=blue]
> well that's what I was trying (and I guess not succeeding at very well)
> I thought if I reduced the question down to one field in one table that
> would be a bite sized piece appropriate to this venue.
>
> When I tried to present the 'overall' 'schema' map to get pointers on
> tables/relationships it seemed to be too big a question for this type of
> forum.
> That's why i've tried to back up and just ask what I thought were very
> small, tightly focused, oversimplified example questions.
>
> I've been reading hundreds of articles, posts, etc about database design/
> normalization / relationships etc
> but since this is all new I'm still just beginning to get a glimmer of what
> my structure will really be.
> I also tried to hint around at being willing to pay someone for some one on
> one tutoring/mentoring in terms of the overall plan but got no nibbles on
> that either.
> I'm clear on what data I need to store, what questions I need to ask of it,
> and what reports I need to be able to generate. I think I'm somewhat clear
> on what tables will need to be created to accomodate that in a normalized
> fashion, and to some extent what fields each table will need to include(at
> least initially).
> I'm less clear on the exact structure of every possible table and the exact
> syntax of each query, in order to get those questions and answers processed.
>
> I'd be happy to find that i was wrong and that someone wanted to hear the
> actual overview and give pointers on that as well.
> :-)
>
> Thanks for your help
> Mark[/color]

Show your schema! I do not fear it. It should be obvious right away
if a relational database approach is called for. I like working at the
schema level and know how to write the queries you'll need. Even more
fun is determining in advance if the schema is practical for what you
need and for how the data is input. Hopefully, after reading all those
sources about normalization you'll understand the practical aspects of
adjusting a schema. The good news is that after all the planning and
making some educated guesses about the future the rest is relatively
easy.

James A. Fortune
CDMAPoster@FortuneJames.com

<CDMAPoster@FortuneJames.com> wrote in message
news:1135296403.098200.162500@g43g2000cwa.googlegr oups.com...
[color=blue]
> Show your schema! I do not fear it.[/color]

<yet>
:-)

It should be obvious right away[color=blue]
> if a relational database approach is called for. I like working at the
> schema level and know how to write the queries you'll need. Even more
> fun is determining in advance if the schema is practical for what you
> need and for how the data is input. Hopefully, after reading all those
> sources about normalization you'll understand the practical aspects of
> adjusting a schema. The good news is that after all the planning and
> making some educated guesses about the future the rest is relatively
> easy.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com[/color]

Ok, here goes...

The main objects I see in my overview are:
1) Job
2) Component
3) Client

(the components are physical objects - parts of a building - we design and
document the details describing the objects so they can be manufactured by
our various clients)

So I have three main Tables:
tblJob
(fields: PK fldJobID, fldJobNumber, fldJobName, FK fldClientID (PK from
tblClient) etc)
tblComponent
(fields PK fldComponentID, FK fldJobID(PK from tblJob), FK
fldComponentSubTypeID(PK from tblComponentSubType - <see below>), and many
other data fields)
tblClient
(fields PK fldClientID, fldClientName, fldClientAddress,
fldClientStandards, etc and many other data fields)
(each client has different standards, file templates, ways of doing things
etc. - this can vary how a Component is displayed or the reports which need
to be generated etc)

(sounds simple so far, eh?)
:-)

the topic of Heirarchical objects comes in here somehow because there are
(at least-<for now>) 3 SubTypes of Component...
each Component object would have some common data (common to all Components)
and some data unique to this subtype.
so that gives rise to the need for another table (or so I'm thinking...not
sure how to handle this issue)
(a table of tables?)
tblComponentSubType (whose PK becomes a FK in tblComponent)
(fields: PK fldCompSubTypeID, fldCompSubTypeName,
fldCompSubTypeUniqueValueTable?)
I guess(not sure) that I need 3 "subtables" to hold the unique data fields
for each ComponentSubtype???
if that's correct the above mentioned fldCompTypeUniqueValueTable is a
pointer to each 'subtable'
other wise since a field has to be atomic I'm not sure how to store 5 values
for one type, 3 for another, and 7 for another...(as an arbitrary example)

if the above is correct, then tblComponent has the fields mentioned above, a
PK, a FK pointing to what Job it's associated with, and a FK pointing to
what ComponentSubType it is and then the remaining fields are the data
fields which are common to all components.

I'm primarily wanting to get one of the SubTypes up and running first, and
then later deal with the other two in their turns so for now I'll talk only
of ComponentSubTypeA.(CompA for short)

There are three general ways data will get input
1) some data items for a given record will be entered by a user in some way
(form with textbox, combobox, option button etc)
2) some data items will be filled out by code which reads some files and
gathers some data and has to input it into the database in the right place.
3) some fields will be calculated values after the previous two entries are
complete, then i need to loop back through the database, reading the filled
out fields and calculate some derived values from those fields.
as for those calculated values the question of whether to store them in the
database or just do it on the fly for generating reports is an open question
and the answer may vary from one data field to another one...that is another
big question I have not settled on (for now in the following description
I'll assume i'm storing those values in a field)

assuming I have a table: tblCompA it will have(among others) the following
fields
fldFamily (string) 'to which family does this belong "a", "b", "c" etc up to
"aa" - "az" - "zz" etc
one job may have only a couple families, another job could have hundreds of
families.
each family happens to be a description of it's cross sectional shape which
also determines other qualities like area, etc.
so lets say I have a piece which is an "a" family.
that piece has a left end and a right end. Each end can be one of 11 (at
this point) possible shapes, square, mitered, finished etc.
I call this data "feature"
(that's what my original post was about, this one data field in this one
table and how to track the various possibilities)
so I'm assuming I want a table or tables to store the 'legal' options for
'features'
thus table: tblFeature or as I think someone was suggesting tblLeftFeature,
tblRightFeature
one aspect I'm not clear on, is that I have a static list of possible
features...
but on any given job, I may only have instances of one or two or a dozen of
the possible combinations of features
so that's why I was originally thinking of an alphanumeric identifier like
"LaRa" or "Lb2Ra1" (variations of features)
so in tblCompA I would have field fldFeature whose value would be "LaRa"
Then I can ask the question, for Job 05100 how many "A" family pieces do I
have with feature "LaRa"
something like Select * From tblCompA where fldJobId = 05100 and fldFamily =
"a" and fldFeature = "LaRa"

but wait it gets still more complicated...
:-)
(like I said its' hard to keep this short and sweet once i get into the
whole enchilada)

Now I have the components of a job divided by Family and Feature, each
component has a Length value.
lets say one component or group of components are 24", some are 36" and some
are 48",
after all entries are made with initial datavalues of Family, Feature, and
Length I need to sort them by length and assign an alphanumeric descriptor
"ComponentName"
so I need to group all Components WHERE fldFamily = "a" AND fldFeature =
"LaRa" get a list of all the lengths,

Once I have the list of all lenght I have to sort by longest to shortest,
Find the longest length that piece or group of pieces then gets a name "A1"
for family "A" and length = longest.
something like
dim idx as integer
idx = 1
SELECT MAX fldLength From tlbComponent WHERE fldFamily = "whatever" AND
fldFeature = "something" etc. then fldCompName = fldFamily & idx
then idx = idx + 1 and continue looping through till they are all accounted
for
Then the next length the name becomes "A2" etc down to the shortest length
..
Then for each different FeatureSet I have to do the same dividing by length,
assigning of a name, and continue through all families and all featuresets
for the given job.

Once all those "ComponentNames" are assigned, I have to loop back through
all the records and a data field can get filled out in the table tblCompA
which is fldCompName for each record in the table so now each record in the
table has a Jobname, and a ComponentName (as well as many other data items)
but those two uniquely identify each common item or group of items on the
job.

Once that happens I can ask for a report like give me a list of all
CompNames and their respective counts for job "xyz".
(that's one of the primary reports I need to generate.)

Thats' an example of one of the calculated values that I think I need to
store in the database rather than just put in a report because that name is
a Critical piece of information to us and goes in various places in
drawings, documents, etc.
Once they are assigned and released into the wild they cant be changed and
if new information comes in, new names might have to be generated which may
deviate from the original ideal that the pieces are named in sequence by
length so in a later stage of a job we may have "out of sequence"
pieces...for that reason the original ComponentNames have to become static
at some point in the process.(jobs may in some cases be released in stages)

There are, of course, other data fields as well,
for example: for a given family(cross section), the cross section has an
area value(double).
so that calls for a table of families
tblFamily
(fields : PK fldFamilyID, fldFamilyName ("a", "b" etc), fldFamilyArea
(double), fldFamilyDescription (string)
which means the field in tblCompA above the field fldFamily (instead of
holding the string name would I suppose be a FK holding the fldFamilyID from
tblFamily primary key)(if I'm understanding the idea of normalizing right)

another calculated value is to get the volume of the piece which is it's
length times it's area (with possible adjustment depending on the "features"
it's left and right end may or may not have)
so for example
I would need to do a loop like
For each oRecordSet in tblCompA
For each fldFamilyID in tblFamily
WHERE tblCompA.fldFamilyID = tblFamily.fldFamilyID _
For each fldLength in tblLengths
AND tblCompA.fldLength = fldLength
And tblCompA.fldFeature = "straight-both-ends"(no adjustment
for end features)
THEN tblCompA.fldCubicFeet = (tblFamily.fldAreaDouble ) X
(fldLength )
or something like that....

does that give you some idea of a part of what i'm trying to do???

Thanks in advance for any input or guidance you wish to offer.
Sorry this got so long winded...but I really appreciate your offer to look
at this overview.

:-)

Mark

MP wrote:[color=blue]
> <CDMAPoster@FortuneJames.com> wrote in message
> news:1135296403.098200.162500@g43g2000cwa.googlegr oups.com...
>[color=green]
> > Show your schema! I do not fear it.[/color]
>
> <yet>
> :-)
>
> It should be obvious right away[color=green]
> > if a relational database approach is called for. I like working at the
> > schema level and know how to write the queries you'll need. Even more
> > fun is determining in advance if the schema is practical for what you
> > need and for how the data is input. Hopefully, after reading all those
> > sources about normalization you'll understand the practical aspects of
> > adjusting a schema. The good news is that after all the planning and
> > making some educated guesses about the future the rest is relatively
> > easy.
> >
> > James A. Fortune
> > CDMAPoster@FortuneJames.com[/color]
>
>
> Ok, here goes...
>
> The main objects I see in my overview are:
> 1) Job
> 2) Component
> 3) Client
>
> (the components are physical objects - parts of a building - we design and
> document the details describing the objects so they can be manufactured by
> our various clients)
>
> So I have three main Tables:
> tblJob
> (fields: PK fldJobID, fldJobNumber, fldJobName, FK fldClientID (PK from
> tblClient) etc)
> tblComponent
> (fields PK fldComponentID, FK fldJobID(PK from tblJob), FK
> fldComponentSubTypeID(PK from tblComponentSubType - <see below>), and many
> other data fields)
> tblClient
> (fields PK fldClientID, fldClientName, fldClientAddress,
> fldClientStandards, etc and many other data fields)
> (each client has different standards, file templates, ways of doing things
> etc. - this can vary how a Component is displayed or the reports which need
> to be generated etc)
>
> (sounds simple so far, eh?)
> :-)
>
> the topic of Heirarchical objects comes in here somehow because there are
> (at least-<for now>) 3 SubTypes of Component...
> each Component object would have some common data (common to all Components)
> and some data unique to this subtype.
> so that gives rise to the need for another table (or so I'm thinking...not
> sure how to handle this issue)
> (a table of tables?)
> tblComponentSubType (whose PK becomes a FK in tblComponent)
> (fields: PK fldCompSubTypeID, fldCompSubTypeName,
> fldCompSubTypeUniqueValueTable?)
> I guess(not sure) that I need 3 "subtables" to hold the unique data fields
> for each ComponentSubtype???
> if that's correct the above mentioned fldCompTypeUniqueValueTable is a
> pointer to each 'subtable'
> other wise since a field has to be atomic I'm not sure how to store 5 values
> for one type, 3 for another, and 7 for another...(as an arbitrary example)
>
> if the above is correct, then tblComponent has the fields mentioned above, a
> PK, a FK pointing to what Job it's associated with, and a FK pointing to
> what ComponentSubType it is and then the remaining fields are the data
> fields which are common to all components.
>
> I'm primarily wanting to get one of the SubTypes up and running first, and
> then later deal with the other two in their turns so for now I'll talk only
> of ComponentSubTypeA.(CompA for short)
>
> There are three general ways data will get input
> 1) some data items for a given record will be entered by a user in some way
> (form with textbox, combobox, option button etc)
> 2) some data items will be filled out by code which reads some files and
> gathers some data and has to input it into the database in the right place.
> 3) some fields will be calculated values after the previous two entries are
> complete, then i need to loop back through the database, reading the filled
> out fields and calculate some derived values from those fields.
> as for those calculated values the question of whether to store them in the
> database or just do it on the fly for generating reports is an open question
> and the answer may vary from one data field to another one...that is another
> big question I have not settled on (for now in the following description
> I'll assume i'm storing those values in a field)
>
> assuming I have a table: tblCompA it will have(among others) the following
> fields
> fldFamily (string) 'to which family does this belong "a", "b", "c" etc up to
> "aa" - "az" - "zz" etc
>,,,[/color]

At first glance, this seems much easier to do than I expected. It's
certainly much easier than some databases I've been asked to design.
Unless I discover a major surprise, this problem should lend itself
well to the relational approach. I'll be off until Tuesday so expect
some feedback by Wednesday. I may request some sample table records
later. Thanks for posting this interesting problem.

James A. Fortune
CDMAPoster@FortuneJames.com

Peace and goodwill.

<CDMAPoster@FortuneJames.com> wrote in message
news:1135367453.066373.308070@f14g2000cwb.googlegr oups.com...[color=blue]
> MP wrote:[color=green]
> > <CDMAPoster@FortuneJames.com> wrote in message
> > news:1135296403.098200.162500@g43g2000cwa.googlegr oups.com...
> >[color=darkred]
> > > Show your schema! I do not fear it.[/color][/color][/color]

[color=blue][color=green]
> > Ok, here goes...
> >
> > The main objects I see in my overview are:
> > 1) Job
> > 2) Component
> > 3) Client[/color][/color]

[color=blue]
> At first glance, this seems much easier to do than I expected. It's
> certainly much easier than some databases I've been asked to design.
> Unless I discover a major surprise, this problem should lend itself
> well to the relational approach. I'll be off until Tuesday so expect
> some feedback by Wednesday. I may request some sample table records
> later. Thanks for posting this interesting problem.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com
>
> Peace and goodwill.[/color]

Sounds great. I really appreciate your looking at this.
Hope you had a good weekend.
Look forward to further conversations.
:-)
Mark

<CDMAPoster@FortuneJames.com> wrote in message
news:1135367453.066373.308070@f14g2000cwb.googlegr oups.com...[color=blue]
> MP wrote:[color=green]
> > <CDMAPoster@FortuneJames.com> wrote in message
> > news:1135296403.098200.162500@g43g2000cwa.googlegr oups.com...
> >[color=darkred]
> > > Show your schema! I do not fear it.[/color]
> >[/color][/color]

on the subject of heirarchical objects heres some thoughts I've come up
with(guesses)
curious if I'm even close on this kind of layout

on the topic of Heirarchical objects
my current guess how to handle this

abbreviations/assumptions
PK = PrimaryKey
FK = ForeignKey
all PK are Unique, either

autoincremented, code incremented or code assigned guid (to be determined)

given:
there's a base object with 3 variations
(not sure if there's a standard accepted term for "base"... "master, main,
parent...whatever)
In the context of classes i think it would be called a base class with
subclasses deriving or
inheriting from that base object

In the context of database tables it may be called Parent and the sub types
children?
not sure about the terminology.

in any case, in my example the base object is called Component
there are 3 SubTypes of Component...
Precast
Caststone
Hardware

Hardware is also heirarchical as there are 3 subtypes of hardware,
Embed
Panel
Loose

my current guess as to how to deal with subtypes is as follows.

Tables:
1)table for base class data
(tblComponent)
2)table for subtypes
(tblComponentSubType)
3)table for subtype1 data
(tblComponentPrecast)
4)table for subtype2 data
(tblComponentCastStone)
5)table for subtype3 data
(tblComponentHardware)

in the base class table:
(tblComponent)
Fields:
PK: fldComponentID
FK: fldComponentSubType
(pointer to subtype table entry)
(this field is PK in tblComponentSubType)
(other fields include data common to all Components)

in tblComponentSubType
Fields:
PK: fldComponentSubTypeID
fldComponentSubTypeTableName(string name of table for this subtype)
at this time there are only 3 records mentioned above

then for each record in tblComponentSubType there is a table listing data
for that subtype
tblComponentSubType1
tblComponentSubType2
tblComponentSubType3

in each tblComponentSubTypeX
Fields:
PK: fldComponentSubTypeXID
FK: fldComponentID
(pointer to item in tblComponent - this field is PK in tblComponent)
other fields are data for this subtype

if the above is correct layout my guess as to how to get information on a
given Component whose ComponentID = 1(for example)
'assign variable to Component id we're looking for
Dim lCompID as Long
lCompID = 1

'Find out which subtype table to look in
'get the id for which subtypeTable to look in from the record for this
component

'find the base information on this component from the generic component
table
Set oBaseRecordSet = Select * From tblComponent Where fldComponentID =
lCompID

'out of that recordset get the subtypetable id
'Set tableId = oBaseRecordSet.fldComponentSubTypeID

'using that id get the table name
dim sSubTypeTableName as String
sSubTypeTableName = _
'get the name for the subtype table from the table of subtypes
Select tblComponentSubType.fldComponentSubTypeName _
From tblComponentSubType _
Where tblComponentSubType.fldComponentSubTypeID = tableID

'now you have the name of what table to look in for specialized data
'find the specialized data on this component from the proper subtype table
'assuming we have a collection of tables(remember this is just
pseudocode..im sure theres an sql clause to get this actual statement)

Select * from Tables.Item(sSubTypeTableName) _
where
Tables.Item(sSubTypeTableName).fldComponentID = lCompID

'somehow the above two queries are "added togther" to get the full list of
data for the specific item whose ComponentID = lCompID

am I even close???
Thanks
mark

MP wrote:[color=blue]
> Greets,
> context: vb6/ado/.mdb/jet 4.0 (no access)/sql
>
> beginning learner, first database, planning stages[/color]

You need to be more deliberate. Step 1 was already in the wrong
direction. Step 2 was started before fixing Step 1. Step 3 was
started before nailing down Step 2, much less Step 1. Then you started
in on Step 4. BTW, all these steps, although premature, did give me
details that will help either to come up with a relational schema that
works or to point to something else. I am still at Step 1 (but making
rapid progress) because your description of fldFeature, as Piet pointed
out, looks like the wrong approach. To give you an idea of what I'm
looking at, think of La1Ra looking more like:

tblFeatures
fldFID PK
fldFeatureGroupID Long
fldJobID Long
fldEndDesc Text
fldConditionID Long
fldVariationNumber Long

10 3 13244 L 1 1
11 3 13244 R 1 Null

given:
tblConditions
fldConditionID fldCondition
1 a
2 b
3 c

Then a query looking for fldJobID = 13244 grouped by fldFeatureGroupID
where fldFeatureGroupID = 3 will produce La1Ra (subquery details
eventually). Two more lines in tblFeatures would specify an additional
feature. This is just a small example of where I'm going since this
idea needs to be checked against your entire schema. It's too bad that
you're locked in to your 'hard-coded' initial set of sorted length
values. That's a nasty angle. I'll continue to keep working on this
schema and I'll probably have more questions concerning it. A bonus
for me is that my main employer deals with specifications for right and
left-hand manufactured components. In the meantime, given the hint of
where I'm headed, try to extend this idea, if possible, to having a
fldFamilyID with a fldFamilyName represent the hundreds of possible
families. fldShape can be one of the fields in tblFamilies along with
a field used to designate R or L. One of the beauties of all this is
that you don't need fldCompSubTypeUniqueValueTable. Any family table
containing fldCompSubTypeID as a foreign key will have data that
connects. No downward pointers are required. In fact, if
fldCompSubTypeID is a foreign key in tblFamilies then all the families
(including names) for all the subtypes can be contained in that single
table and fldCompSubTypeID together with fldSubTypeFamilyNumber can be
used to look up the family information relative to a specific subtype.
I.e., each table of components of a given type join tblComponent on
fldComponentSubTypeID and fldFamilyNumber with a different set of
fields available, including all the common ones. In that case
tblFamilies would be a static table used to look up that information
and fldFamilyNumber could be included in tblComponent knowing that
enough information is present to get the family information. Let me
know what you think of that idea. Let's not delve into hierarchic OO
database design unless we need its strengths. So what we're looking at
so far is:

tblConditions
fldConditionID PK
fldCondition Text

tblFeatures
fldFID PK
fldFeatureGroupID Long
fldJobID Long
fldEndDesc Text
fldConditionID Long
fldVariationNumber Long
....

tblJob
fldJobID PK
fldJobNumber Long
fldJobName Text
fldClientID Long
....

tblClient
fldClientID PK
fldClientName Text
fldClientAddress Text
fldClientCity Text
....

tblClientStandards
CSID PK
fldClientID FK
fldStandardName Text
....

tblComponent
fldComponentID PK
fldJobID FK
fldComponentSubTypeID FK
fldFamilyNumber FK
fldComponentName Text
fldComponentLength Double
fldComponentUnits Text

tblComponentSubType
fldCompSubTypeID PK
fldCompSubTypeName Text

tblFamilies
fldFamilyID PK
fldCompSubTypeID FK
fldFamilyNumber Long
fldFamilyName Text

tblCompA
fldCAID PK
fldComponentSubTypeID FK
fldFamilyNumber FK
fldShape Text
fldCompASpecificField Text

I realize that this is a little abstract and Access-centric, but this
is by far the best place to make design changes. Note that calculated
values can still be done on-the-fly when you're forced to assign A1,
A2, ... Please refer to specific tables/fields in the sample schema
above when discussing the merits or shortcomings of it. Also see if
tblCompA, tblCompB, etc. have enough fields in common to create a
single tblCompDetails containing fldCompType provided there are no
plans to increase the number of component types in the future :-).

I hope this rough outline helps you get started,

James A. Fortune
CDMAPoster@FortuneJames.com

<CDMAPoster@FortuneJames.com> wrote in message
news:1135735646.901046.235860@g49g2000cwa.googlegr oups.com...[color=blue]
> MP wrote:[color=green]
> > Greets,
> > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> >
> > beginning learner, first database, planning stages[/color]
>
> You need to be more deliberate. Step 1 was already in the wrong
> direction. Step 2 was started before fixing Step 1. Step 3 was
> started before nailing down Step 2, much less Step 1. Then you started
> in on Step 4. BTW, all these steps, although premature, did give me
> details that will help either to come up with a relational schema that
> works or to point to something else. I am still at Step 1 (but making
> rapid progress) because your description of fldFeature, as Piet pointed
> out, looks like the wrong approach. To give you an idea of what I'm
> looking at, think of La1Ra looking more like:
>[/color]

Thank you so much for looking at this.
I will study your reply and respond back in detail asap.
Thanks again,
Mark

<CDMAPoster@FortuneJames.com> wrote in message
news:1135735646.901046.235860@g49g2000cwa.googlegr oups.com...[color=blue]
> MP wrote:[color=green]
> > context: vb6/ado/.mdb/jet 4.0 (no access)/sql[/color][/color]

Hi James,

First, thank you for looking at this.

Second, I'm not sure I communicated clearly and/or I'm not sure I'm
understanding clearly the following layout.
(as far as me not having step1 figured out before going to step2...that's
par for the course <vbg>)
I wasn't thinking of them as step wise items, but just an overview of the
different kinds of information I need to deal with.
At this point in my thinking I realize I'm not clear on the final shape of
all the tables....
What I see is that I have a few "separate" issues on which I'm not clear
(well, more than a few probably <vbg>)
(and which are (in my mind) not necessarily step wise dependent on one
another - but maybe they are...that's where my inexperience with databases
may be hampering my understanding of your layout)
....maybe my original post was confusing in this regard
:-)

As I understand your reply, you're looking at tblFamily seems to be the
"Main" repository..with pointers to subtype etc
For some reason I'm looking at tblComponent as being the "main" repository
.... with Family just being a small piece of data about Component.

I see the following 4 points as being Cardinal questions in my problem
domain.

1) whether to store information about every job for every year in one giant
db for the entire company (which would keep getting bigger every job every
year and I would think degrading performance over time...but I'm not
experienced enough in databases to know if that's true)
- or -
to store information which is CompanyWide (list of clients, list of
jobs, etc) in one "master.mdb"
and then to store job specific information about each individual job in
it's own "Job.mdb"

considerations:
- once a job is done there is only a rare occasion that I may have to go
back to a past job looking for information
- once a job is one or two years old it's almost inconceivable we would
need access to that information other than in the sense of archival storage
in the event of some liability question years down the line(and that's
highly unlikely)
- there is no relation ship from job1 to job2 in terms of the data
stored for that job(in the sense of "component data")
(other than the company wide information like list of clients etc,
which does make sense to store in companywide database)
- we, at the present time, have no questions we ask which compare one
job to another...they are very much stand alone projects
(although I do understand that could evolve as we learn more about
the power of databases...assuming I keep my job that long<vbg>)

2) how to store information about a general class of object"Component" which
may be one of three different subtypes
considerations:
- some data will be the same for every subtype and some will be
different for each subtype
(that's why I thought (based on my limited studies of database
theory) that a heirarchical relationship existed)
(but I have no idea how to *implement* that heirarchical
relationship in database terms - I was just imagining it would be a "common"
table and 3 "sub" tables)
I'm fine with your suggestion to *not* make it heirarchical...I just
don't know what the alternative is...

I can easily accept the answer that I just need three different tables
one for each type and forget the one "universal" table
and just accept that the three tables will have some fields which repeat
from one to the next...I guess that's not really duplicating data (in the
sense of normalization to prevent duplcation of data)
Its' just duplication of *dataFields* not duplication of *dataValues*
so maybe that's ok...
(actually there is probably much more information that is different from
one type to another so maybe the commonComponent table is really a waste of
resources)

3) How to store "family" information
considerations:
- FamilyName "A" has no intrinsic meaning...it is an arbitrary selection
from a list of "legal" selections relative only to a given job.
- once the familyName "A" is assigned for Job 12345, it will then be
related to some information, like shape, volume, weightperinch etc on a job
by job basis
- for job# 12344, family "A" has no relationship to family "A" for job#
12345
- family "P" for subtypeOne has no relationship at all to family "P" for
subtypeTwo on the same job number 12345
(assuming that family"P" is a legal familyname for both subtypes for
a given (job/client))

- the family names are originated on the fly as a job progresses,
I find one kind of piece, I name it "A"(or whatever the actual
designation becomes...which also varies by Client...based on their
various standards for naming convention of their pieces)
then I find the next piece, I name it "B" etc...
maybe job 12344 has only A-C but job 12345 has A-Z

(so the possible list of legal family names may also have to be stored
in a table somewhere with a fldClientId pointing to a table of clients
then in the table of clients there may be a pointer to the table of
possible family names)
Like I said I'm not sure how all this wants to look in it's final form.

4) How to store "feature" information - (the original "partial" question)

Part of my problem lies in the fact that I'm not experienced in databases so
my thinking does not automatically arrange itself in appropriate units in
all probability
My thinking is arranged around the real world units (Components) with which
I am dealing.
As such the "base" unit is Component.
The Features discussed below are a fact about a Component.
However the table structure is arranged and however the queries need to be
written, each Component needs one entry in some table(or Tables) which
completely describes that one component. Since there are 3 subtypes of
components my thinking was that I would have to pull information from at
least two tables (commontable, subtypetable) to get a full view of one
component. (see my post @ 12/27/05 1:47pm yesterday about the heirarchical
nature of my objects)

The "Features" described below only apply to one or possibly two of the (3)
SubTypes of Component.

One of the facts about that one component is what do it's two ends look like
(granted there are two ends but there is only one component)
so the combined facts about Left end condition and Right end condition
devolve into (somehow) One fact about One component.

[color=blue]
>To give you an idea of what I'm
> looking at, think of La1Ra looking more like:
>[/color]

----------------------------------------------------------------------------
-[color=blue]
> tblFeatures[/color]
Yes
--------------------------------[color=blue]
> fldFID PK[/color]
Yes, meaningless Unique Long or GUID Key
--------------------------------[color=blue]
> fldFeatureGroupID Long[/color]
not sure what fldFeatureGroupID is
it looks like it would be a FK to a tblFeatureGroup PK fldFeatureGroupID
I'm not clear how you're using FeatureGroup here...
I think the fldFeatureCondition you have below is what I'm calling Feature
Maybe FeatureGroup and FeatureCondition are one and the same thing?

--------------------------------[color=blue]
> fldJobID Long[/color]
In my original schema I was considering using a separate .mdb file for each
job we do.
We store all files re a given job in a "job folder" on the server.
It made sense to me to store a .mdb file there as well.
However, when asking about the merits of PerJob or PerCompany .mdb
philosophy I was recommended to use PerCompany.
That to me complicates the layout of the .mdb, making it a huge database
storing all information about all jobs we do.
But since that was what was recommended, that is the direction I'm trying to
go.
The reason I mention the Job question is that you have fldJobID in
tblFeatures.
I thought fldJobId would be in tblComponent.
In other words, a JobId is a fact about a component, and a feature is also a
fact about a component, but a JobId is not really a fact about a Feature.(at
least in my mind) perhaps you can comment on that relationship in your
schema below.
In my understanding to get information about a job, I would filter
tblComponent on fldJobID, then filter that result on whatever other criteria
applied for the specific query.

--------------------------------[color=blue]
> fldEndDesc Text[/color]
this would imply I would need two records for each Component, one whose
fldEndDesc = "L" and another for fldEndDesc = "R"
what about if I had two fields...fldEndLeft and fldEndRight...wouldn't that
allow me to have only one record per piece?
(and again, I think these fields would be in tblComponent or
tblComponentSubTypeOne but not in tblFeature)
--------------------------------[color=blue]
> fldConditionID Long[/color]
--------------------------------[color=blue]
> fldVariationNumber Long
>
> 10 3 13244 L 1 1
> 11 3 13244 R 1 Null
>
> given:
> tblConditions
> fldConditionID fldCondition
> 1 a
> 2 b
> 3 c[/color]

[color=blue]
>
> Then a query looking for fldJobID = 13244 grouped by fldFeatureGroupID
> where fldFeatureGroupID = 3 will produce La1Ra (subquery details
> eventually).[/color]

While my queries will always be filtered for just one job at a time, I think
of the primary object to query is Component
So I think of the query as (pseudo sql)<g>
Select "allComponents" where fldJobId = "currentJob"
Now I have all components for Job 13244
Now from those components I have to sort them by "FeatureSet"
Select "allComponents" from "PreviousSet" Where fldFeatureLeft = "A" And
fldFeatureRight = "A"
Now I have all components whose leftEnd = a and whose Right end = a
Now I can sort Those Components by Length...

Two more lines in tblFeatures would specify an additional[color=blue]
> feature. This is just a small example of where I'm going since this
> idea needs to be checked against your entire schema.[/color]
I'm not understanding some things here...:-(
hopefully (if I haven't worn out my welcome already <g>) some further
conversation will clarify

It's too bad that[color=blue]
> you're locked in to your 'hard-coded' initial set of sorted length
> values. That's a nasty angle. I'll continue to keep working on this
> schema and I'll probably have more questions concerning it. A bonus
> for me is that my main employer deals with specifications for right and
> left-hand manufactured components.[/color]

I think somewhere in the following is where I'm not understanding exactly
how you're dividing up the information
I'm sure you're correct in your assesment...I'm just not "getting" it yet...
I'll keep working on it and maybe if you respond to my responses you can
help me understand why this scenario is better than how I'm thinking of
having different tables for each subtype

In the meantime, given the hint of[color=blue]
> where I'm headed, try to extend this idea, if possible, to having a
> fldFamilyID with a fldFamilyName represent the hundreds of possible
> families. fldShape can be one of the fields in tblFamilies along with
> a field used to designate R or L. One of the beauties of all this is
> that you don't need fldCompSubTypeUniqueValueTable. Any family table
> containing fldCompSubTypeID as a foreign key will have data that
> connects. No downward pointers are required. In fact, if
> fldCompSubTypeID is a foreign key in tblFamilies then all the families
> (including names) for all the subtypes can be contained in that single
> table and fldCompSubTypeID together with fldSubTypeFamilyNumber can be
> used to look up the family information relative to a specific subtype.
> I.e., each table of components of a given type join tblComponent on
> fldComponentSubTypeID and fldFamilyNumber with a different set of
> fields available, including all the common ones. In that case
> tblFamilies would be a static table used to look up that information
> and fldFamilyNumber could be included in tblComponent knowing that
> enough information is present to get the family information. Let me
> know what you think of that idea. Let's not delve into hierarchic OO
> database design unless we need its strengths. So what we're looking at
> so far is:
>[/color]
----------------------------------------------------------------------------
-[color=blue]
> tblConditions[/color]
not sure where you're going with this one...is this the variations per given
feature? like La1 versus La ?[color=blue]
> fldConditionID PK
> fldCondition Text[/color]

----------------------------------------------------------------------------
-[color=blue]
> tblFeatures[/color]

I'm still not clear on this one...see comments above[color=blue]
> fldFID PK[/color]
Yes, meaningless Unique Long or GUID Key[color=blue]
> fldFeatureGroupID Long
> fldJobID Long
> fldEndDesc Text
> fldConditionID Long
> fldVariationNumber Long
> ...
>[/color]
----------------------------------------------------------------------------
-[color=blue]
> tblJob[/color]
Yes[color=blue]
> fldJobID PK[/color]
Yes[color=blue]
> fldJobNumber Long[/color]
Yes[color=blue]
> fldJobName Text[/color]
Yes[color=blue]
> fldClientID Long[/color]
Yes[color=blue]
> ...[/color]
----------------------------------------------------------------------------
-[color=blue]
> tblClient[/color]
Yes[color=blue]
> fldClientID PK[/color]
Yes[color=blue]
> fldClientName Text[/color]
Yes[color=blue]
> fldClientAddress Text[/color]
Yes[color=blue]
> fldClientCity Text[/color]
Yes
----------------------------------------------------------------------------
-[color=blue]
> tblClientStandards[/color]
Yes[color=blue]
> CSID PK[/color]
Yes[color=blue]
> fldClientID FK[/color]
Yes[color=blue]
> fldStandardName Text[/color]
Yes
----------------------------------------------------------------------------
-[color=blue]
> tblComponent[/color]
Yes, I see the need for a table of components (this holds all data which is
common to all component sub-types (in my thinking)...if that's correct?)
This is in my thinking the "Main"/"Master" (whatever) table in the whole
database....all other tables exist to clarify the data about this component
--------------------------------[color=blue]
> fldComponentID PK[/color]
Yes
--------------------------------[color=blue]
> fldJobID FK[/color]
Yes
--------------------------------[color=blue]
> fldComponentSubTypeID FK[/color]
Yes
--------------------------------[color=blue]
> fldFamilyNumber FK[/color]
not sure but wouldn't this be fldFamilyID from tblFamilies below?
--------------------------------[color=blue]
> fldComponentName Text[/color]
yes, this is the final name derived/calculated/stored data after all
components are entered/sorted/and named
--------------------------------[color=blue]
> fldComponentLength Double[/color]
Yes
--------------------------------[color=blue]
> fldComponentUnits Text[/color]
Not sure what this one is...is it a description of what units the length
value above is measured in??? eg("feet" or "inches" or "meters" etc)??? if
so that's a good idea which I had not considered...we usually would measure
it in inches but that 's good to make it a mutable field for future
growth/adaptation
[color=blue]
>[/color]

----------------------------------------------------------------------------
-[color=blue]
> tblComponentSubType[/color]
Yes, I see the need for a table separating the 3 subtypes of components

--------------------------------[color=blue]
> fldCompSubTypeID PK[/color]
Yes

--------------------------------[color=blue]
> fldCompSubTypeName Text[/color]
Yes
(this table would - at current point - have 3 entries "Precast" "CastStone"
"Hardware")

does this relate to my other post @ 12/27/05 1:47pm yesterday about the
heirarchical nature of my objects?
ie base object = Component
sub object = ComponentSubType
(one of the subobjects is Hardware...which also has subtypes
(HardwareStruct, HardwarePanel, HardwareLoose)

----------------------------------------------------------------------------
-[color=blue]
> tblFamilies[/color]
----yes I see the need for tblFamilies (but it may vary per client and per
job)
this table holds the "legal" family names
this has to relate back to tblClient somehow as each client may have
different list of Legal family names
for example
clientOne disallows certain letters...ie don't use DIOQSUV
clientTwo only disallows DIOQ
etc
so it's almost like I need a tblFamily for each client in tblClients or a
pointer to a list of disallowed letters...or something
In the user operating the program he should be able to enter JobNumber
(that will automatically setup which client we're dealing with)
then he enters (lets' say there's a button on a form to run the sub
"GetNextFamily" and the next un-used family name pops up out of the legal
list for that client(job)
or perhaps a combo box is shown with a list of all legal names for that job
and he selects from that....
so there's kind of like two groups of family names for a given job - the
legal list of all possible choices(at start of job)...and the resulting list
of all used choices on this job.(at end of job)
so maybe I need two tables - tblFamilyLegalList and tblFamilyUsedList...???
just guessing here
and again there may be multiple tblFamilyLegalLists (one for each client)(or
some clients may share the same list so it's not necessarily one to one
relationship...maybe there are 3 lists and 10 clients and each client gets a
pointer to one of the three lists...something like that

--------------------------------[color=blue]
> fldFamilyID PK[/color]
yes, I see the need for a PK ID field

--------------------------------[color=blue]
> fldCompSubTypeID FK[/color]

Here, I'm not sure why fldCompSubTypeID is in tblFamily....perhaps you can
illuminate your thinking in this item
I think fldCompSubTypeID would be in tblComponent...
in other words "what subtype of component is this component?" is a fact
about a component - not a fact about a familyname
although, all components will have a familyname
though how they are named may vary by what subtype they are
and also on a given job I may have a ComponentSubTypeOne(Caststone item)
with a family name of "P" and a ComponentID of "1" so the components
"fullname" would be = "P1"
and on the same job I may also have a ComponentSubTypeThree(hardware item)
and for that client, his standard for hardware cast into a panel might be
"P" so one item of hardware for that job may also be named "P1"
thus the family names for each componentSubType need to be segregated
somehow....
which I understand would be accommodated by your fldCompSubTypeID so maybe
that's the secret ingredient...I need to gestate on this one for a while I
think....I just don't understand yet why store it in tblFamily rather than
in tblComponent

is simplicity morphing into complexity yet? :-)
-------------------------------[color=blue]
> fldFamilyNumber Long[/color]
I don't see the need of a fldFamilyNumber, but maybe you see a need for this
I'm missing, could you elaborate?
I think this may be a duplicate of the PK field above??? (fldFamilyID)
--------------------------------[color=blue]
> fldFamilyName Text[/color]
Yes, this is my primary data(fact about component)
this is one record in the list of legal names right?

----------------------------------------------------------------------------
-[color=blue]
> tblCompA[/color]
Yes, if this is one of the Heirarchical subtype tables? but you said forget
heirarchy for now so maybe it's not???
If it is, i would have tblCompA, tblCompB, and tblCompC for the three
different subtypes??? is that right?
--------------------------------[color=blue]
> fldCAID PK[/color]
Yes
--------------------------------[color=blue]
> fldComponentSubTypeID FK[/color]
This seems like the samething as fldCAID above???
--------------------------------[color=blue]
> fldFamilyNumber FK[/color]
I would think this would be in the 'main' component table (tblComponent)
since all components would have this data
but I'm not sure if you're using my idea for how to divide "heirarchical"
entities as in my other post
--------------------------------[color=blue]
> fldShape Text[/color]
derived from fldFamilyNumber above...I would think this would be in
tblFamilyName...ie what shape does family"A" refer to on Job 12345
--------------------------------[color=blue]
> fldCompASpecificField Text[/color]
Yes, if this is the "heirarchical" table for this sub type then I would have
multiple fields like this describing the facts about this component fo this
sub type

I think in this subtype table (and all other subtype tables) I also need a
pointer to which piece(component) is being described
so I would add a field here
fldCompID FK (PK from tblComponent.fldCompID)

does that sound right?

[color=blue]
>
> I realize that this is a little abstract and Access-centric, but this
> is by far the best place to make design changes. Note that calculated
> values can still be done on-the-fly when you're forced to assign A1,
> A2, ...[/color]

I realize they *can* be calculated on the fly, but once done they become
immutable...if later entries are added after a part of a job has been
released, the calculations for already released pieces cant' change...only
new items can be added and if they are 'out of sequence' then they have to
be so identified in some fashion...that's why I thought I would store this
data after the initial calculation was done.
so on any further re-calculation(if pieces were added) I would check this
field for "NULL" and if it was Not Null then I leave it alone...
does that make sense???

Please refer to specific tables/fields in the sample schema[color=blue]
> above when discussing the merits or shortcomings of it. Also see if
> tblCompA, tblCompB, etc. have enough fields in common to create a
> single tblCompDetails containing fldCompType provided there are no
> plans to increase the number of component types in the future :-).
>
> I hope this rough outline helps you get started,
>[/color]

again, I can't thank you enough for helping me to think this through..
I relize this is a big complicated affair well beyond the usual short
question answer on this forum...thats why my initial hesitation
let me know If I'm overstepping my bounds on this forum
I really appreciate your willingness to help.
I also understand this is more than a 'small' question and if you wish to
deal with this in a way other than on ng let me know.

Thanks
Mark Propst
(mark atsymbol atreng dotsymbol com)
(I'm not as brave as you are if that's your real email <vbg> though even
without printing email address those spammers have no problem finding me
anyway so don't know how much good my paranoia does <g>)

[color=blue]
> James A. Fortune
> CDMAPoster@FortuneJames.com
>[/color]

MP wrote:[color=blue]
> <CDMAPoster@FortuneJames.com> wrote in message
> news:1135735646.901046.235860@g49g2000cwa.googlegr oups.com...[color=green]
> > MP wrote:[color=darkred]
> > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql[/color][/color]
>
> Hi James,
>
> First, thank you for looking at this.
>
> Second, I'm not sure I communicated clearly and/or I'm not sure I'm
> understanding clearly the following layout.
> (as far as me not having step1 figured out before going to step2...that's
> par for the course <vbg>)
> I wasn't thinking of them as step wise items, but just an overview of the
> different kinds of information I need to deal with.
> At this point in my thinking I realize I'm not clear on the final shape of
> all the tables....
> What I see is that I have a few "separate" issues on which I'm not clear
> (well, more than a few probably <vbg>)
> (and which are (in my mind) not necessarily step wise dependent on one
> another - but maybe they are...that's where my inexperience with databases
> may be hampering my understanding of your layout)
> ...maybe my original post was confusing in this regard
> :-)
>
> As I understand your reply, you're looking at tblFamily seems to be the
> "Main" repository..with pointers to subtype etc
> For some reason I'm looking at tblComponent as being the "main" repository
> ... with Family just being a small piece of data about Component.
>
> I see the following 4 points as being Cardinal questions in my problem
> domain.
>
> 1) whether to store information about every job for every year in one giant
> db for the entire company (which would keep getting bigger every job every
> year and I would think degrading performance over time...but I'm not
> experienced enough in databases to know if that's true)
> - or -
> to store information which is CompanyWide (list of clients, list of
> jobs, etc) in one "master.mdb"
> and then to store job specific information about each individual job in
> it's own "Job.mdb"
>,,,
> Thanks
> Mark Propst
> (mark atsymbol atreng dotsymbol com)
> (I'm not as brave as you are if that's your real email <vbg> though even
> without printing email address those spammers have no problem finding me
> anyway so don't know how much good my paranoia does <g>)
>
>[color=green]
> > James A. Fortune
> > CDMAPoster@FortuneJames.com
> >[/color][/color]

Mark,

It looks like I'm going to have to go into much more detail about this
than I imagined. Let me make a few short comments and then I'll need
some time to give you a more detailed explanation in another post. If
you don't mind I'll start back at Step 1 :-).

I'll start with the decision to use two records to store each
component. That's a good observation and a good question. You'll note
that having a separate table called tblConditions allows you to have as
many conditions as you want without having to deal with a fixed list of
combinations (or unfixed list if any new conditions are added). Also
note that having tblFeatures allows for as many Features as you want to
be attached to the job, namely, a Group of features. Since the
tblFeatures.fldFID is a primary key (probably AutoNumber),
fldFeatureGroupID allows you to distinguish all the various Features
assigned to a given job. Although your observation is correct that
adding more fields would allow you to add a feature using a single
record (think molecule), I decided that each end constituted a more
fundamental decision (think atom). What questions do you need to ask
yourself in deciding whether to use one record or two records to store
a feature? The first question would be something like, "Is the RxmLyn
way of assigning features ever going to change?" The second, more
pragmatic question, would be something like, "Will I ever need to query
summary information about just one side or the other?" and, if so,
"Does a particular table layout make doing something like Totals any
easier?" If you feel quite confident that those questions have been
considered you can decide whether to use a single record or to use two
records to store your Features. Note that the single record schema
also allows you to do most summary queries that you're likely to
encounter. I have found in the past that having more records rather
than more fields gives me more flexibility to deal with unexpected
future requirements. One record per Feature is fine once you've
considered the ramifications. Note that with the single record schema
another category in addition to R and L, if that's even possible,
requires adding more fields to tblFeatures and could complicate/limit
totals queries even more.

My last few comments are more general. I would not give each job its
own mdb. Access can handle putting all the jobs in one mdb. At first
I looked at tblFamily as being a main repository and changed that to
tblComponent and other tables as being the main repository. I see
tblFamily as mostly fixed. Give me another day or so to come up with a
more detailed explanation about why I started where I did.

James A. Fortune
CDMAPoster@FortuneJames.com

CDMAPoster@FortuneJames.com wrote in
news:1135816735.360600.132710@g44g2000cwa.googlegr oups.com:
[color=blue][color=green][color=darkred]
>> > MP wrote:
>> > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql[/color]
>>
>> Hi James,
>>
>> First, thank you for looking at this.
>>[/color]
> Mark,
>
> It looks like I'm going to have to go into much more detail about this
> than I imagined. Let me make a few short comments and then I'll need
> some time to give you a more detailed explanation in another post. If
> you don't mind I'll start back at Step 1 :-).
>
> I'll start with the decision to use two records to store each
> component. That's a good observation and a good question. You'll
> note that having a separate table called tblConditions allows you to
> have as many conditions as you want without having to deal with a
> fixed list of combinations (or unfixed list if any new conditions are
> added).[/color]

I think we're getting real close to something I can understand.
I need to make sure I'm understanding your terminology though.
I think your tblConditions is what I originally thought of as
tblFeature...ie a table of possible end
"features"...actually I like your term "Condition" even better than my term
"feature"
But then you also talk about 'feature' as well so I'm not sure how you're
dividing these two terms.
To make sure I'm following you I will try to describe again the data I need
to store so we can use the same terminology and I can be more sure i'm
understanding your layout.

Initially I was trying to evolve my plan from general to specific.
My basic unit is a component.
There are three different kinds of component.
So I was thinking in an object oriented way that they were subtypes of an
"ideal" "concept" of component
But maybe that is not the way to go with this database.
maybe i should let go of the concept of subtypes with a universal parent and
just say I have three different kinds of objects I need to track in
completely separate ways.

so for now I'll ignore the possibility that there are two other subtypes I
need to deal with and just talk about ComponentOne (Comp1) for now.
The subject of "end condition" or "end feature" only applies to Comp1

I have a piece of stone (Comp1).
The piece of stone has a shape.
I need to identify all possible variations using a common system so I can
divide up stones into "matching" shapes.

Most(but not all) instances of Comp1 are linear in nature, that is, they are
longer in one axis than in the other two, like an extruded bar of aluminum
for example.
That's why I started describing them by saying they have a "crosssection"
(Family) and a Length.

Now if they were all squared off at the ends they would only need two pieces
of information to identify common pieces, Family and length.
Unfortunately, they aren't all squared off at the end, some are mitered,
some have finished ends, some have return ends etc
Thats what I originally called "Feature" or "End Feature" or "FeatureCode"

This data comes from a fixed list of possibilities
(11 basic options at this point - maybe more in the future)
(Maybe that calls for two tables...one to hold the list of all
possibilities, and one for the possibilities which actually occurred on this
job...not sure about that yet)

Now that you bring in the term "Condition" I prefer that term to "Feature"
So from here on out I will use the term "EndCondition"
And due to it's (usually) linear nature it has two "EndConditions" one Left
and one Right.
so now to describe the piece I have
Family,LeftEndCondition,RightEndCondition, and Length.

In rare cases the end condition could have a variation.
Lets say 90% of the pieces have "Straight" end conditions.
But two of those pieces have a small notch cut out of one end.
So I have to allow for an unknown infinitely variable possibility of
variations for any given "EndCondition"
So I was just using a numeric modifier to indicate variation (if it
occurred)
Once a variation is assigned it has to be stored for that job so if another
piece is found with the exact same variation, the original identifier will
be used but if another variation is found, the next identifier will be added
to the table of EndConditionVariations
Since the variation is unknown and infinitely variable I figure I'll allow
the user to enter some short text description like "notch 2 X 2 X 2 top
front corner"
that description will then be linked with a numeric identifier and can be
seen in a combo box or list somewhere for the user to see as they identify
other pieces they come upon.
Since the variation is unknown and infinitely variable it is not coming from
a fixed list of possibilities like the EndConditions are...it has to be user
entered in the simplest possible way, and then stored so they can see what
variations they have already created in case they come up with another
instance of the same variation they dont' recreate it with a new name, they
select the one they already have which again applies to their current
piece - or if it's a new variation they create a new entry in that table of
variations

So now I have Family, LeftEndCondition, LeftEndVariation(maybe),
RightEndCondition, RightEndVariation(maybe), and length.

Now I think I have a full description of the piece which would allow for any
possibility.

Maybe that is what you are calling the "FeatureGroup"??? (which is what I
originally started out calling a FeatureCode since I was using a single
concatenated string to identify those 4 pieces of data - not counting family
and length - )

The combination of those (possibly) 6 pieces of data...(4 if there are no
variations...or the variations can be a Null value and just leave it at 6
pieces of data) appear to be sufficient to describe one piece

that above description works for the vast majority of instances of Comp1
Except what about the pieces which are not linear in morphology, for example
a round column, or a square post cap....not sure how to generalize the
system to allow for all possible shapes.
Maybe I need another layer of discrimination...like linear group, column
group, rectangular group
I can see this is tricky trying to come up with a generalized system to
describe any possible shape of object.
Maybe i need an exception class...????

In any case, leaving aside the question of the exceptions and just dealing
with the linear group, I say I have 6 pieces of data to represent the
morphology of one instance of Comp1.

I cant' seem to get away from my concept of a database table...
I think of the table as representing a "kind of a thing"(entity)
I think of a Record as being an "Instance of a thing"
and I think of a Field as being a "Fact about this instance of this thing"
That's why I cant' get it out of my head that I need to store left and right
end information in the record that describes the "thing" ("Component")
I'm not trying to be stubborn I'm just not understanding how to get away
from storing the information about the Components left and right end
conditions in the record about the component.
But please don't give up on me,,,I want to learn...if you can explain a
better way I'm all ears

So how about this idea?
tblJob
fldJobID PK
fldClientID FK (from tblClient.fldClientID PK)
...all other fields
tblClient
fldClientID PK
...all other fields

tblComponent
fldCompId PK
fldJobID FK (entry from tblJob.fldJobID)
fldFamilyNameUsedID FK (entry from tblFamilyNameUsed PK) (can not be
NULL)(see table below)
fldLeftEndConditionID FK (entry from tblLeftEndCondition PK) (can not be
NULL)
fldRightEndConditionID FK (entry from tblRightEndCondition PK) (can not
be NULL)
fldLeftEndVariationID FK (entry from tblLeftEndVariation PK) (can be
NULL)
fldRightEndVariationID FK (entry from tblRightEndVariation PK) (can be
NULL)
fldCompLength (I don't think I need a table of lengths cause they're
infinitely variable...just have to be > some minimum legal value which can
be checked in some kind of data validation routine)
...all other fields

....not sure about this idea...the next two tables are
repetitive...duplicates...one for left and one for right
....the reason I did this was so the Component record could have a FK whose
name matches the PK in each of the next two tables (I think the FK and it's
associated PK have to be named identically but I'm not sure....everything
I've read shows it like that but I've not seen it explicitly spelled out
that that was a requirement)
(philosophically speaking<g> it seems wasteful and redundant...but since
there are only a dozen approx items in each table as far as resources go
it's nothing compared to the hundreds of thousands of entries in the
tblComponent over all jobs over all years of operation so maybe it's not so
bad)

tblLeftEndCondition
fldLeftEndConditionID PK
fldLeftEndConditionDescription (these dont' need to be this long in
reality but for clarity I'll try to not abbreviate here)
fldLeftEndConditionAbbreviation
...that table has 11 entries at this point...more can be added if need
arises in future
(i don't think I need a FK fldJobId here cause these are just a standard
list of choices available for any job)

tblRightEndCondition
fldRightEndConditionID PK
fldRightEndConditionDescription (these dont' need to be this long in
reality but for clarity I'll try to not abbreviate here)
fldRightEndConditionAbbreviation
...same 11 entries as left table above

....again, two identical tables...one for left and one for right???
....again, just so the FK of parent is name same as PK of child(or is that
visa versa?)
(this table may or may not have any entries for a given job)
tblLeftEndVariation
fldLeftEndVariationID PK
fldLeftEndVariationDescription (entered by user, displayed in list
somewhere or combo box etc)
fldJobID FK (from tblJob)
(here I put a reference to what job since the variations have to be
identified on a per job basis)

(this table may or may not have any entries for a given job)
tblLeftRightVariation
fldRightEndVariationID PK
fldRightEndVariationDescription (entered by user, displayed in list
somewhere or combo box etc)
fldJobID FK (from tblJob)

Although your observation is correct that[color=blue]
> adding more fields would allow you to add a feature using a single
> record (think molecule), I decided that each end constituted a more
> fundamental decision (think atom). What questions do you need to ask
> yourself in deciding whether to use one record or two records to store
> a feature? The first question would be something like, "Is the RxmLyn
> way of assigning features ever going to change?"[/color]

possible if I completetly redesigned my thinking and program but it works
for me so at this point I dont see a need to revise it.
Regardless of how the information is stored in the database it's useful to
me in other parts of the program to have a 10 character(plus or minus)
"FeatureCode" to enter in certain places in other files/drawings/drawing
objects in order to "encapsulate" the full description of the "character" of
the piece for the user.
so even if I store the individual pieces of data in different tables or
different fields for the sake of database operations...I'll probably be
getting that data out of the database and recombining it into a short
'FeatureCode' for use elsewhere.... or possibly be extracting the
"featureCode' from already existing files/drawing objects via code, then
breaking the string up into it's component parts and storing them in their
respective places in the database(wherever we end up deciding to store them)
(I originally used LaRa as an abbreviated way to describe a simple feature
code...in actual practice the code is made up of the abbreviations for the
various end conditions we've identified. eg "Straight" = "ST"
so the simplest piece(the vast majority) will be "LSTRST" and is human
readable by the user instantly to see that it's a piece with a left straight
end and a right straight end.
likewise if "Return End" = "RE" then LSTRRE = left straight right return ...
since we only have a few variations and in happy coincidence their first two
letters are all unique among the 11 variations, its' an easy string
operation in vb to grab the abbreviations from the descriptions and visa
versa and it's easy for the human user to also interpret it at a glance with
a minimum of training.

:-)

all I'm doing with it is saying this piece has (as another example) a
straight left end and a
mitered right end (LSTRMI) so that defines a certain morphology of an object
and
the concatenated string was just a convenient (in my mind) way to
encapsulate that data into a small chunk of data I could pass around the
program for the purpose of grouping objects according to their shape.
That idea came to me before realizing that a database would be another
improvement in the ability of my program to track data...so now this is a
whole new world of how to group 'facts' and I'm trying to catch up to you
in how you're thinking of storing these different 'facts'

(I am currently storing and sorting the familyname, featureCode, and Length
in Scripting.Dictionaries and collections...the idea of moving to database
makes it much more powerful and flexible...that's just a little history on
how i got to this point in the first place)

The second, more[color=blue]
> pragmatic question, would be something like, "Will I ever need to
> query summary information about just one side or the other?"[/color]

I can't at this point imagine any possible reason to do this...all I need
to track is the piece(Component) itself. The two ends are inextricably
attached to the Component and have no meaning separated from said
Component. (I never need to know how many Left miters - irrespective of
right hand conditions - I have) (I only need to know about whole
Components)...at least at this point I cant' imagine what I would do with
that finer granularity of data...that doesn't mean the need cant' arise
in the future...so I appreciate the ideas that database storage is
opening up to me in how one can separate various nuances of a piece of
data.

and, if[color=blue]
> so, "Does a particular table layout make doing something like Totals
> any easier?" If you feel quite confident that those questions have
> been considered you can decide whether to use a single record or to
> use two records to store your Features. Note that the single record
> schema also allows you to do most summary queries that you're likely
> to encounter.[/color]

I have found in the past that having more records[color=blue]
> rather than more fields gives me more flexibility to deal with
> unexpected future requirements.[/color]

That is a good piece of advice I'm taking to heart

One record per Feature is fine once[color=blue]
> you've considered the ramifications. Note that with the single record
> schema another category in addition to R and L, if that's even
> possible, requires adding more fields to tblFeatures and could
> complicate/limit totals queries even more.
>[/color]

well, maybe I'll think of some reason to identify "middle" condition of
piece...maybe it has a lump in the center of it...so that's a good
thought, even though at this point 99% of all pieces will only be
watching their Right and left ends I do like the open ended flexibility
of using Records instead of Fields to allow for future
expansion/modification.

[color=blue]
> My last few comments are more general. I would not give each job its
> own mdb. Access can handle putting all the jobs in one mdb.[/color]

That's what everyone else says also so I'm accepting the concept.
Since I'm new to database it seems odd...I would think that after
hundreds of pieces per job over hundreds of jobs per year over 10 or 20
years that database is going to get pretty big...
I would also think, but maybe it's not true, that the bigger the database
got, the slower things would run, but maybe that's just my proceedural
thinking at work.
;-)

At first[color=blue]
> I looked at tblFamily as being a main repository and changed that to
> tblComponent and other tables as being the main repository.[/color]

That jives more with how I see it also.

I see[color=blue]
> tblFamily as mostly fixed.[/color]

Ok I see two tables for Family.
One holds the legal names allowed on a per client(job) basis.
Actually it either holds the legal letters or the disallowed letters
depending on how I want to code the selection process...either way it ends
up the same functionality

The other table holds the "Used up" letters (or combination of letters)
which are currnently in use on a per job basis.

so
....this is a static table...all legal names for all clients grouped into one
table(is this what's called a Look up table???)
tblFamilyNameAllowed
fldFamilyNameAllowedID PK
fldFamilyNameAllowedText
fldClientID FK (from tblClient PK - each record identifies the client
for whom this is allowed (or not allowed)

tblFamilyNameUsed
fldFamilyNameUsedID PK
fldFamilyNameUsedText
fldJobId FK (from tblJobList PK...all used families can be found on a
per job basis this way I think)
...I suppose I could add a field here to hold each componentId that uses
this family name but I'm not sure I need that or how I would use it...just a
thought...you may see a way to use it with your knowledge of sql...
example: fldComponentIDThatUsesThisFamilyName FK (from tblComponent)????
....that's kind of like a cross referencing index...the tblComponent has a
pointer to this table and this table has a pointer back to the
tblComponent...don't know enough about db to know if thats useful or
stupid!<g>

or something like that

so when I start a job and need a family name ...
in my most fluent sql pseudo code <vbg>
I say, I'm working on Job number "ThisJobID" (which identifies which client
it is since client id is fk in tblJobList)
so look in tblFamilyNameAllowed where fldClientID = tblJobList.fldClientID
And tblJobList.tblJobID = "ThisJobID" and if that entry does not appear in
tblFamilyNameUsed Where tblFamilyNameUsed.fldJobID = "ThisJobID", then use
this entry, Else ..."getNextEntryForThisJobId"

does that sound right???

Give me another day or so to come up with[color=blue]
> a more detailed explanation about why I started where I did.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com
>
>[/color]

Again, I can't thank you enough for helping me to think through all this.
As you can imagine, if it's making you scratch your head just a
little...imagine my confusion when I'm completely new to databases! I don't
feel quite so bad now that I didn't come up with the final solution in five
minutes on my own, now that I'm getting confirmation that there is at least
a tiny bit of complexity here to figuring it all out.

Looking forward to your further ideas.
Kind regards,
Mark

MP wrote:[color=blue]
> CDMAPoster@FortuneJames.com wrote in
> news:1135816735.360600.132710@g44g2000cwa.googlegr oups.com:
>[color=green][color=darkred]
> >> > MP wrote:
> >> > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> >>
> >> Hi James,
> >>
> >> First, thank you for looking at this.
> >>[/color]
> > Mark,
> >
> > It looks like I'm going to have to go into much more detail about this
> > than I imagined. Let me make a few short comments and then I'll need
> > some time to give you a more detailed explanation in another post. If
> > you don't mind I'll start back at Step 1 :-).
> >
> > I'll start with the decision to use two records to store each
> > component. That's a good observation and a good question. You'll
> > note that having a separate table called tblConditions allows you to
> > have as many conditions as you want without having to deal with a
> > fixed list of combinations (or unfixed list if any new conditions are
> > added).[/color]
>
> I think we're getting real close to something I can understand.
> I need to make sure I'm understanding your terminology though.
> I think your tblConditions is what I originally thought of as
> tblFeature...ie a table of possible end
> "features"...actually I like your term "Condition" even better than my term
> "feature"
> But then you also talk about 'feature' as well so I'm not sure how you're
> dividing these two terms.
> To make sure I'm following you I will try to describe again the data I need
> to store so we can use the same terminology and I can be more sure i'm
> understanding your layout.
>
> Initially I was trying to evolve my plan from general to specific.
> My basic unit is a component.
> There are three different kinds of component.
> So I was thinking in an object oriented way that they were subtypes of an
> "ideal" "concept" of component
> But maybe that is not the way to go with this database.
> maybe i should let go of the concept of subtypes with a universal parent and
> just say I have three different kinds of objects I need to track in
> completely separate ways.
>
> so for now I'll ignore the possibility that there are two other subtypes I
> need to deal with and just talk about ComponentOne (Comp1) for now.
> The subject of "end condition" or "end feature" only applies to Comp1
>
> I have a piece of stone (Comp1).
> The piece of stone has a shape.
> I need to identify all possible variations using a common system so I can
> divide up stones into "matching" shapes.
>
> Most(but not all) instances of Comp1 are linear in nature, that is, they are
> longer in one axis than in the other two, like an extruded bar of aluminum
> for example.
> That's why I started describing them by saying they have a "crosssection"
> (Family) and a Length.
>
> Now if they were all squared off at the ends they would only need two pieces
> of information to identify common pieces, Family and length.
> Unfortunately, they aren't all squared off at the end, some are mitered,
> some have finished ends, some have return ends etc
> Thats what I originally called "Feature" or "End Feature" or "FeatureCode"
>
> This data comes from a fixed list of possibilities
> (11 basic options at this point - maybe more in the future)
> (Maybe that calls for two tables...one to hold the list of all
> possibilities, and one for the possibilities which actually occurred on this
> job...not sure about that yet)
>
> Now that you bring in the term "Condition" I prefer that term to "Feature"
> So from here on out I will use the term "EndCondition"
> And due to it's (usually) linear nature it has two "EndConditions" one Left
> and one Right.
> so now to describe the piece I have
> Family,LeftEndCondition,RightEndCondition, and Length.
>
> In rare cases the end condition could have a variation.
> Lets say 90% of the pieces have "Straight" end conditions.
> But two of those pieces have a small notch cut out of one end.
> So I have to allow for an unknown infinitely variable possibility of
> variations for any given "EndCondition"
> So I was just using a numeric modifier to indicate variation (if it
> occurred)
> Once a variation is assigned it has to be stored for that job so if another
> piece is found with the exact same variation, the original identifier will
> be used but if another variation is found, the next identifier will be added
> to the table of EndConditionVariations
> Since the variation is unknown and infinitely variable I figure I'll allow
> the user to enter some short text description like "notch 2 X 2 X 2 top
> front corner"
> that description will then be linked with a numeric identifier and can be
> seen in a combo box or list somewhere for the user to see as they identify
> other pieces they come upon.
> Since the variation is unknown and infinitely variable it is not coming from
> a fixed list of possibilities like the EndConditions are...it has to be user
> entered in the simplest possible way, and then stored so they can see what
> variations they have already created in case they come up with another
> instance of the same variation they dont' recreate it with a new name, they
> select the one they already have which again applies to their current
> piece - or if it's a new variation they create a new entry in that table of
> variations
>
> So now I have Family, LeftEndCondition, LeftEndVariation(maybe),
> RightEndCondition, RightEndVariation(maybe), and length.
>
> Now I think I have a full description of the piece which would allow for any
> possibility.
>
> Maybe that is what you are calling the "FeatureGroup"??? (which is what I
> originally started out calling a FeatureCode since I was using a single
> concatenated string to identify those 4 pieces of data - not counting family
> and length - )
>
> The combination of those (possibly) 6 pieces of data...(4 if there are no
> variations...or the variations can be a Null value and just leave it at 6
> pieces of data) appear to be sufficient to describe one piece
>
> that above description works for the vast majority of instances of Comp1
> Except what about the pieces which are not linear in morphology, for example
> a round column, or a square post cap....not sure how to generalize the
> system to allow for all possible shapes.
> Maybe I need another layer of discrimination...like linear group, column
> group, rectangular group
> I can see this is tricky trying to come up with a generalized system to
> describe any possible shape of object.
> Maybe i need an exception class...????
>
> In any case, leaving aside the question of the exceptions and just dealing
> with the linear group, I say I have 6 pieces of data to represent the
> morphology of one instance of Comp1.
>
> I cant' seem to get away from my concept of a database table...
> I think of the table as representing a "kind of a thing"(entity)
> I think of a Record as being an "Instance of a thing"
> and I think of a Field as being a "Fact about this instance of this thing"
> That's why I cant' get it out of my head that I need to store left and right
> end information in the record that describes the "thing" ("Component")
> I'm not trying to be stubborn I'm just not understanding how to get away
> from storing the information about the Components left and right end
> conditions in the record about the component.
> But please don't give up on me,,,I want to learn...if you can explain a
> better way I'm all ears
>
> So how about this idea?
> tblJob
> fldJobID PK
> fldClientID FK (from tblClient.fldClientID PK)
> ...all other fields
> tblClient
> fldClientID PK
> ...all other fields
>
> tblComponent
> fldCompId PK
> fldJobID FK (entry from tblJob.fldJobID)
> fldFamilyNameUsedID FK (entry from tblFamilyNameUsed PK) (can not be
> NULL)(see table below)
> fldLeftEndConditionID FK (entry from tblLeftEndCondition PK) (can not be
> NULL)
> fldRightEndConditionID FK (entry from tblRightEndCondition PK) (can not
> be NULL)
> fldLeftEndVariationID FK (entry from tblLeftEndVariation PK) (can be
> NULL)
> fldRightEndVariationID FK (entry from tblRightEndVariation PK) (can be
> NULL)
> fldCompLength (I don't think I need a table of lengths cause they're
> infinitely variable...just have to be > some minimum legal value which can
> be checked in some kind of data validation routine)
> ...all other fields
>
> ...not sure about this idea...the next two tables are
> repetitive...duplicates...one for left and one for right
> ...the reason I did this was so the Component record could have a FK whose
> name matches the PK in each of the next two tables (I think the FK and it's
> associated PK have to be named identically but I'm not sure....everything
> I've read shows it like that but I've not seen it explicitly spelled out
> that that was a requirement)
> (philosophically speaking<g> it seems wasteful and redundant...but since
> there are only a dozen approx items in each table as far as resources go
> it's nothing compared to the hundreds of thousands of entries in the
> tblComponent over all jobs over all years of operation so maybe it's not so
> bad)
>
> tblLeftEndCondition
> fldLeftEndConditionID PK
> fldLeftEndConditionDescription (these dont' need to be this long in
> reality but for clarity I'll try to not abbreviate here)
> fldLeftEndConditionAbbreviation
> ...that table has 11 entries at this point...more can be added if need
> arises in future
> (i don't think I need a FK fldJobId here cause these are just a standard
> list of choices available for any job)
>
> tblRightEndCondition
> fldRightEndConditionID PK
> fldRightEndConditionDescription (these dont' need to be this long in
> reality but for clarity I'll try to not abbreviate here)
> fldRightEndConditionAbbreviation
> ...same 11 entries as left table above
>
> ...again, two identical tables...one for left and one for right???
> ...again, just so the FK of parent is name same as PK of child(or is that
> visa versa?)
> (this table may or may not have any entries for a given job)
> tblLeftEndVariation
> fldLeftEndVariationID PK
> fldLeftEndVariationDescription (entered by user, displayed in list
> somewhere or combo box etc)
> fldJobID FK (from tblJob)
> (here I put a reference to what job since the variations have to be
> identified on a per job basis)
>
> (this table may or may not have any entries for a given job)
> tblLeftRightVariation
> fldRightEndVariationID PK
> fldRightEndVariationDescription (entered by user, displayed in list
> somewhere or combo box etc)
> fldJobID FK (from tblJob)
>
>
> Although your observation is correct that[color=green]
> > adding more fields would allow you to add a feature using a single
> > record (think molecule), I decided that each end constituted a more
> > fundamental decision (think atom). What questions do you need to ask
> > yourself in deciding whether to use one record or two records to store
> > a feature? The first question would be something like, "Is the RxmLyn
> > way of assigning features ever going to change?"[/color]
>
> possible if I completetly redesigned my thinking and program but it works
> for me so at this point I dont see a need to revise it.
> Regardless of how the information is stored in the database it's useful to
> me in other parts of the program to have a 10 character(plus or minus)
> "FeatureCode" to enter in certain places in other files/drawings/drawing
> objects in order to "encapsulate" the full description of the "character" of
> the piece for the user.
> so even if I store the individual pieces of data in different tables or
> different fields for the sake of database operations...I'll probably be
> getting that data out of the database and recombining it into a short
> 'FeatureCode' for use elsewhere.... or possibly be extracting the
> "featureCode' from already existing files/drawing objects via code, then
> breaking the string up into it's component parts and storing them in their
> respective places in the database(wherever we end up deciding to store them)
> (I originally used LaRa as an abbreviated way to describe a simple feature
> code...in actual practice the code is made up of the abbreviations for the
> various end conditions we've identified. eg "Straight" = "ST"
> so the simplest piece(the vast majority) will be "LSTRST" and is human
> readable by the user instantly to see that it's a piece with a left straight
> end and a right straight end.
> likewise if "Return End" = "RE" then LSTRRE = left straight right return ...
> since we only have a few variations and in happy coincidence their first two
> letters are all unique among the 11 variations, its' an easy string
> operation in vb to grab the abbreviations from the descriptions and visa
> versa and it's easy for the human user to also interpret it at a glance with
> a minimum of training.
>
> :-)
>
>
> all I'm doing with it is saying this piece has (as another example) a
> straight left end and a
> mitered right end (LSTRMI) so that defines a certain morphology of an object
> and
> the concatenated string was just a convenient (in my mind) way to
> encapsulate that data into a small chunk of data I could pass around the
> program for the purpose of grouping objects according to their shape.
> That idea came to me before realizing that a database would be another
> improvement in the ability of my program to track data...so now this is a
> whole new world of how to group 'facts' and I'm trying to catch up to you
> in how you're thinking of storing these different 'facts'
>
> (I am currently storing and sorting the familyname, featureCode, and Length
> in Scripting.Dictionaries and collections...the idea of moving to database
> makes it much more powerful and flexible...that's just a little history on
> how i got to this point in the first place)
>
> The second, more[color=green]
> > pragmatic question, would be something like, "Will I ever need to
> > query summary information about just one side or the other?"[/color]
>
> I can't at this point imagine any possible reason to do this...all I need
> to track is the piece(Component) itself. The two ends are inextricably
> attached to the Component and have no meaning separated from said
> Component. (I never need to know how many Left miters - irrespective of
> right hand conditions - I have) (I only need to know about whole
> Components)...at least at this point I cant' imagine what I would do with
> that finer granularity of data...that doesn't mean the need cant' arise
> in the future...so I appreciate the ideas that database storage is
> opening up to me in how one can separate various nuances of a piece of
> data.
>
>
> and, if[color=green]
> > so, "Does a particular table layout make doing something like Totals
> > any easier?" If you feel quite confident that those questions have
> > been considered you can decide whether to use a single record or to
> > use two records to store your Features. Note that the single record
> > schema also allows you to do most summary queries that you're likely
> > to encounter.[/color]
>
>
> I have found in the past that having more records[color=green]
> > rather than more fields gives me more flexibility to deal with
> > unexpected future requirements.[/color]
>
> That is a good piece of advice I'm taking to heart
>
> One record per Feature is fine once[color=green]
> > you've considered the ramifications. Note that with the single record
> > schema another category in addition to R and L, if that's even
> > possible, requires adding more fields to tblFeatures and could
> > complicate/limit totals queries even more.
> >[/color]
>
> well, maybe I'll think of some reason to identify "middle" condition of
> piece...maybe it has a lump in the center of it...so that's a good
> thought, even though at this point 99% of all pieces will only be
> watching their Right and left ends I do like the open ended flexibility
> of using Records instead of Fields to allow for future
> expansion/modification.
>
>[color=green]
> > My last few comments are more general. I would not give each job its
> > own mdb. Access can handle putting all the jobs in one mdb.[/color]
>
> That's what everyone else says also so I'm accepting the concept.
> Since I'm new to database it seems odd...I would think that after
> hundreds of pieces per job over hundreds of jobs per year over 10 or 20
> years that database is going to get pretty big...
> I would also think, but maybe it's not true, that the bigger the database
> got, the slower things would run, but maybe that's just my proceedural
> thinking at work.
> ;-)
>
>
> At first[color=green]
> > I looked at tblFamily as being a main repository and changed that to
> > tblComponent and other tables as being the main repository.[/color]
>
>
> That jives more with how I see it also.
>
>
> I see[color=green]
> > tblFamily as mostly fixed.[/color]
>
> Ok I see two tables for Family.
> One holds the legal names allowed on a per client(job) basis.
> Actually it either holds the legal letters or the disallowed letters
> depending on how I want to code the selection process...either way it ends
> up the same functionality
>
> The other table holds the "Used up" letters (or combination of letters)
> which are currnently in use on a per job basis.
>
> so
> ...this is a static table...all legal names for all clients grouped into one
> table(is this what's called a Look up table???)
> tblFamilyNameAllowed
> fldFamilyNameAllowedID PK
> fldFamilyNameAllowedText
> fldClientID FK (from tblClient PK - each record identifies the client
> for whom this is allowed (or not allowed)
>
> tblFamilyNameUsed
> fldFamilyNameUsedID PK
> fldFamilyNameUsedText
> fldJobId FK (from tblJobList PK...all used families can be found on a
> per job basis this way I think)
> ...I suppose I could add a field here to hold each componentId that uses
> this family name but I'm not sure I need that or how I would use it...just a
> thought...you may see a way to use it with your knowledge of sql...
> example: fldComponentIDThatUsesThisFamilyName FK (from tblComponent)????
> ...that's kind of like a cross referencing index...the tblComponent has a
> pointer to this table and this table has a pointer back to the
> tblComponent...don't know enough about db to know if thats useful or
> stupid!<g>
>
> or something like that
>
> so when I start a job and need a family name ...
> in my most fluent sql pseudo code <vbg>
> I say, I'm working on Job number "ThisJobID" (which identifies which client
> it is since client id is fk in tblJobList)
> so look in tblFamilyNameAllowed where fldClientID = tblJobList.fldClientID
> And tblJobList.tblJobID = "ThisJobID" and if that entry does not appear in
> tblFamilyNameUsed Where tblFamilyNameUsed.fldJobID = "ThisJobID", then use
> this entry, Else ..."getNextEntryForThisJobId"
>
> does that sound right???
>
> Give me another day or so to come up with[color=green]
> > a more detailed explanation about why I started where I did.
> >
> > James A. Fortune
> > CDMAPoster@FortuneJames.com
> >
> >[/color]
>
> Again, I can't thank you enough for helping me to think through all this.
> As you can imagine, if it's making you scratch your head just a
> little...imagine my confusion when I'm completely new to databases! I don't
> feel quite so bad now that I didn't come up with the final solution in five
> minutes on my own, now that I'm getting confirmation that there is at least
> a tiny bit of complexity here to figuring it all out.
>
> Looking forward to your further ideas.
> Kind regards,
> Mark[/color]

I see that the initial schema provided a basis for discussion but not
exactly in the way I intended. I'll try to digest the information you
just provided and generate a new sample schema that is more limited.
That will provide a starting point for adding the rest of the schema in
future posts. Getting the best schema you can up front is crucial for
avoiding problems later. Experience just helps you know which ones are
likely to cause problems. In your case, designing a schema is not
complex at all -- unless you want a good one. Actually it's mostly
experience and thinking ahead mixed with some planning. You may have a
tiger by the tail. You're certainly going to need a decent plan to
pull it off. This problem has you looking like a puppy trying to get
its teeth around a large cowbone. BTW, when the A1, A2, ... are
assigned, Access can put those names in a different field.

James A. Fortune
CDMAPoster@FortuneJames.com

I know of a shop where you can have the volumes of this thread
leather-bound at a good price.

HTH!

Kyle

<CDMAPoster@FortuneJames.com> wrote in message
news:1135967495.183128.114040@f14g2000cwb.googlegr oups.com...[color=blue]
>
> I see that the initial schema provided a basis for discussion but not
> exactly in the way I intended.[/color]

Am I just being dense and not getting something?
[color=blue]
> I'll try to digest the information you
> just provided and generate a new sample schema that is more limited.
> That will provide a starting point for adding the rest of the schema in
> future posts. Getting the best schema you can up front is crucial for
> avoiding problems later. Experience just helps you know which ones are
> likely to cause problems. In your case, designing a schema is not
> complex at all -- unless you want a good one. Actually it's mostly
> experience and thinking ahead mixed with some planning. You may have a
> tiger by the tail. You're certainly going to need a decent plan to
> pull it off. This problem has you looking like a puppy trying to get
> its teeth around a large cowbone.[/color]
[color=blue]
> BTW, when the A1, A2, ... are
> assigned, Access can put those names in a different field.[/color]

Right. (That's one of the (several) ..."other fields as needed"...referred
to previously)

NB I've been trying to make it clear that I'm not using Access.
Since I was also (cross)posting to an access group as well as the ado groups
I tried to be clear about that.
"context: vb6/ado/.mdb/jet 4.0 (no access)/sql"

Ps
I just went back and reviewed my original post and see that I used the term
Condition and Feature interchangably...I didnt' realize that when I later
thought You were introducing a new term. Sorry for the confusion. (I guess
at the time i unconsciously realized that Condition was a better description
than Feature)
:-)

Thanks again for all your help.
I hope Lyle isn't going to get too mad at you for helping me.

I'm not mad at all, just astonished at Jame's patience and
perseverance.

MP wrote:

<snip>
[color=blue]
> NB I've been trying to make it clear that I'm not using Access.
> Since I was also (cross)posting to an access group as well as the ado groups
> I tried to be clear about that.
> "context: vb6/ado/.mdb/jet 4.0 (no access)/sql"[/color]

<snip>
[color=blue]
> Thanks again for all your help.
> I hope Lyle isn't going to get too mad at you for helping me.[/color]

Are you getting any input from any of the other groups you have
posted to?

No?

That's because you haven't explained what a 'component' is or even
what the purpose of storing one is. You have only tried to explain
your own attempt at designing tables.

You must be handling this information in some way now.
How?

When you view a 'report' on a component, what does it look like
now? Maybe post a picture of a few for download somewhere (not as an
attachment here) if you can.

The only way I know of 'normalizing' data is to start from a
required/desired view of that data.

"rkc" <rkc@rochester.yabba.dabba.do.rr.bomb> wrote in message
news:bgztf.47195$XJ5.25586@twister.nyroc.rr.com...[color=blue]
>
>
>
> MP wrote:
>
> <snip>
>[color=green]
> > NB I've been trying to make it clear that I'm not using Access.
> > Since I was also (cross)posting to an access group as well as the ado[/color][/color]
groups[color=blue][color=green]
> > I tried to be clear about that.
> > "context: vb6/ado/.mdb/jet 4.0 (no access)/sql"[/color]
>
> <snip>
>[color=green]
> > Thanks again for all your help.
> > I hope Lyle isn't going to get too mad at you for helping me.[/color]
>
> Are you getting any input from any of the other groups you have
> posted to?
>
> No?
>
> That's because you haven't explained what a 'component' is or even
> what the purpose of storing one is.[/color]

my apologies if I wasn't clear on that.
In my first post I was striving for brevity.
after getting the ok from James to post an overview I went into more detail
(apparently too much detail for Lyle <g>) for example my post of 12/22/05 at
9:02 above has what I thought was a fairly clear, if abbreviated, overview
of my general problem domain (even though I'm still leaving out a lot of
details that I'll have to figure out eventually...I thought I should pare
things down to just the basics to keep it as brief as possible)

[color=blue]
> You have only tried to explain
> your own attempt at designing tables.[/color]

Well, again, sorry if I posted inappropriately, in most of the other code
ngs people like to see that the op is at least trying to do something on
their own and then asking for help
like, this is what I'm trying..what am I doing wrong or how can this be
done better...
that's what my intention was in showing how I imagined (in my admitted
inexperience) a table design might look like.
Not sure how I should be approaching this if everything I've done so far has
been wrong!
:-)
[color=blue]
>
> You must be handling this information in some way now.
> How?
>[/color]

as I mentioned in my post of 12/30/05 at 1:21am
"(I am currently storing and sorting the familyname, featureCode, and Length
in Scripting.Dictionaries and collections...the idea of moving to database
makes it much more powerful and flexible..."
[color=blue]
> When you view a 'report' on a component, what does it look like
> now? Maybe post a picture of a few for download somewhere (not as an
> attachment here) if you can.[/color]

I havent' yet designed the reports...at this point our company doesnt' use
databases at all.
We're a small company, (no one here is a professional programmer). I'm
trying to learn ways to improve how they do things now.
We deal with (cad) drawings. Our end product is a dwg of a building with
each piece labled with a name or piecemark. A1, A2, B1, B2 etc. We also
have to derive a list of how many A1's A2's etc. Right now it's all done by
"hand", someone looks at the dwg, counts the pieces and assembles a count
"manually".
I'm trying (on my own time, and not being a professional programmer) to
develop a system that will be of use to the company in automating to some
extent and eliminating some human error.
[color=blue]
> The only way I know of 'normalizing' data is to start from a
> required/desired view of that data.
>[/color]

Though it's probably not what you would consider a "Report" as such, the
information I want to extract has two levels of detail...one for the job as
a whole and the other for each individual piece on the job.
Individual:
Piecemark: A1
SectionFileName: "A-Section.dwg"
Length: 12"
Count: 24
CuFt: 1
Weight: 150

Job Report:
Job Number: 12345
Piece Count List:
A1 - 24
A2 - 36
B1 - 123
B2 - 3
etc

Although "reports" are apparently the end product of most databases, in our
case I was looking at the possibility of entering items into our drawings
(in various ways using code and the api of our drawing program) by reading
data stored in the database in various tables and putting that data into
different parts of different drawings where it's required. So I don't have
a large list of "Reports" as such which I need to output. I can see that
that will be an added advantage in the future if I can figure out how to
store the data in the first place. Once I started researching databases I
began to see all kinds of possibilities that could improve how we do things.
At this point it's just ideas in my head that I'm trying(with the generous
help of people here) to find ways to implement.

I'm sorry if I've offended anyone here, or misused this public forum.
Since I'm just beginning I don't know how to ask the right questions in the
briefest manner while including all salient information.

I'm certainly open to constructive criticism...even destructive
criticism...what the heck <g>... as long as no one starts hitting <vbg>

Thanks to all for their help and advice
Mark

"Lyle Fairfield" <lylefairfield@aim.com> wrote in message
news:1136046544.000778.299650@z14g2000cwz.googlegr oups.com...[color=blue]
> I'm not mad at all, just astonished at Jame's patience and
> perseverance.[/color]

Yes, I too admire his patience, perseverance and generosity.
Happy New Year
:-)

MP wrote:
[color=blue]
> I'm sorry if I've offended anyone here, or misused this public forum.
> Since I'm just beginning I don't know how to ask the right questions in the
> briefest manner while including all salient information.[/color]

I don't think you have done anything to offend anyone and the last thing
you want to do is get sensitive about any comments you might receive.

I've been following your thread from the beginning and still do not have
a clear picture of what you are hoping to accomplish. No harm done. I
may just be dense. I see now that you are just thinking out loud.

The Denseness sickness is spreading all around the Lake. I have it too.

"rkc" <rkc@rochester.yabba.dabba.do.rr.bomb> wrote in message
news:2ODtf.63528$ME5.28586@twister.nyroc.rr.com...[color=blue]
> MP wrote:
>[color=green]
> > I'm sorry if I've offended anyone here, or misused this public forum.
> > Since I'm just beginning I don't know how to ask the right questions in[/color][/color]
the[color=blue][color=green]
> > briefest manner while including all salient information.[/color]
>
> I don't think you have done anything to offend anyone and the last thing
> you want to do is get sensitive about any comments you might receive.[/color]

Ok, roger that! :-)
I guess I was a little nervous from the beginning due to 3 observations I
considered.
1 I couldn't describe my entire question in a one liner.
2 ng's are primarily geared toward a short snippet level of detail.
3 this being an access group and me using only ado/sql/vb6 and no access
so I didn't mean to get sensitive but i want to be respectful of the others
around here and I'm kinda standing out like a sore thumb cause I'm a rank
beginner and my posts are like, what?, 10 times the average size in kb <vbg>
So actually I thought Lyle's comment about the Leather bounding was pretty
cute and it made me laugh but I was worried he was really upset with me
using too much bandwidth on the group here.
[color=blue]
>
> I've been following your thread from the beginning and still do not have
> a clear picture of what you are hoping to accomplish. No harm done. I
> may just be dense. I see now that you are just thinking out loud.[/color]

apparently my loud thinking is not being very clear in describing the data I
want to store :-)
like I said, its' hard for me as a beginner to know how to phrase my
question about the best way to store this information...I guess my inner
confusion is just filtering out of my fingers onto the keys and ending up
piled over here.
:-)

Thanks for chilling me out.
Mark

<CDMAPoster@FortuneJames.com> wrote in message
news:1135967495.183128.114040@f14g2000cwb.googlegr oups.com...[color=blue][color=green]
> > Although your observation is correct that[color=darkred]
> > > adding more fields would allow you to add a feature using a single
> > > record (think molecule), I decided that each end constituted a more
> > > fundamental decision (think atom).[/color][/color][/color]

Hi James,
You may be tired of hearing from me by now...but in the event you *haven't*
killfiled me ...<g>
I think I'm finally understanding your idea of keeping the data about
"Condition/Feature-and-or-Variations" in a separate table.
Since that table can have a pointer to the componentID, I don't think I need
any entry at all in the tblComponent that refers to Condition/Feature!
I think I'm finally realizing that the sql query will combine the components
data together so they don't actually have to be stored in the same table.
(completely the opposite of the concept of OO where a class encapsulates
it's data...I was thinking of table=class and field=property...so thats why
I kept thinking the tblComponent had to have a field(or fields)describing
it's conditions...)

sorry for being so slow on the uptake.

using your previously supplied schema as a template I have the following
modification to offer for critique
'Table to hold list of possible end conditons
....
tblConditionList
fldConditionID PK
fldCondition Text
eg:
Condition(0) = "STRAIGHT"
Condition(1) = "RETURN"
Condition(2) = "FINISH"
etc
Condition(10) = "SPECIAL"

' since entries in Special category will need to be user defined, stored,
described and shown to user in combobox or other control, it will require
another table to hold it's entries
....
tblConditionSpecial
fldConditionSpecialID PK
fldConditionSpecial Text (user defined description of special condition)
fldComponentID FK <from tblComponent>(which component is this associated
with)

' also since any of the conditions can have a user defined variation we
also need a table,
....
tblConditionVariation
fldConditionVariationID PK
fldConditionVariation Text (user defined description of variation)
fldConditionID FK <from tblCondition> (which condition is this a variation
of)
fldComponentID FK <from tblComponent> (which component is this associated
with)

'the following table is what conditions were encountered...
'so each component gets multiple entries in this table....as many as
required to describe piece.
'that allows for future expansion if you need to add more records per
piece...like one for middle....
'the entries all get linked with the ComponentId FK to determine which group
of features goes with which Component.
'This eliminates need for a field in tblComponent to point to condition for
that component...(i think)
....
tblFeatures (may rename this later...tblConditionActual??? for consistency
of naming convention)
fldFeatureID PK
fldComponentID FK <from tblComponent>...(which component is this record
applied to)
fldEndDesc Text (Left, right, middle, userdefined..etc etc)
(maybe rename the above field to fldConditionLocation for consistency of
naming convention)
fldConditionID FK <from tblConditionList>
fldCondititonVariationID FK <from tblConditionVariation> (will be NULL
unless it's a variation)
fldConditionSpecialID FK <from tblConditionSpecial> (will be NULL unless
it's a "Special")

I think the balance of the schema is perfect and this should be enough to
get me off the ground....(I think)
I'll be very happy to hear any other ideas you may have come up with and/or
comments about this idea, but if I've run out my 'dance card' I think I may
be able to stumble along by myself now...unless you post back saying I still
have it all backward
:-)

Now all I have to do is figuring out all the sql statements to make all this
work!

Thanks for the help and guidance.

Mark

MP wrote:[color=blue]
> <CDMAPoster@FortuneJames.com> wrote in message
> news:1135967495.183128.114040@f14g2000cwb.googlegr oups.com...[color=green][color=darkred]
> > > Although your observation is correct that
> > > > adding more fields would allow you to add a feature using a single
> > > > record (think molecule), I decided that each end constituted a more
> > > > fundamental decision (think atom).[/color][/color]
>
> Hi James,
> You may be tired of hearing from me by now...but in the event you *haven't*
> killfiled me ...<g>
> I think I'm finally understanding your idea of keeping the data about
> "Condition/Feature-and-or-Variations" in a separate table.
> Since that table can have a pointer to the componentID, I don't think I need
> any entry at all in the tblComponent that refers to Condition/Feature!
> I think I'm finally realizing that the sql query will combine the components
> data together so they don't actually have to be stored in the same table.
> (completely the opposite of the concept of OO where a class encapsulates
> it's data...I was thinking of table=class and field=property...so thats why
> I kept thinking the tblComponent had to have a field(or fields)describing
> it's conditions...)
>
> sorry for being so slow on the uptake.
>
> using your previously supplied schema as a template I have the following
> modification to offer for critique
> 'Table to hold list of possible end conditons
> ...
> tblConditionList
> fldConditionID PK
> fldCondition Text
> eg:
> Condition(0) = "STRAIGHT"
> Condition(1) = "RETURN"
> Condition(2) = "FINISH"
> etc
> Condition(10) = "SPECIAL"
>
> ' since entries in Special category will need to be user defined, stored,
> described and shown to user in combobox or other control, it will require
> another table to hold it's entries
> ...
> tblConditionSpecial
> fldConditionSpecialID PK
> fldConditionSpecial Text (user defined description of special condition)
> fldComponentID FK <from tblComponent>(which component is this associated
> with)
>
> ' also since any of the conditions can have a user defined variation we
> also need a table,
> ...
> tblConditionVariation
> fldConditionVariationID PK
> fldConditionVariation Text (user defined description of variation)
> fldConditionID FK <from tblCondition> (which condition is this a variation
> of)
> fldComponentID FK <from tblComponent> (which component is this associated
> with)
>
>
> 'the following table is what conditions were encountered...
> 'so each component gets multiple entries in this table....as many as
> required to describe piece.
> 'that allows for future expansion if you need to add more records per
> piece...like one for middle....
> 'the entries all get linked with the ComponentId FK to determine which group
> of features goes with which Component.
> 'This eliminates need for a field in tblComponent to point to condition for
> that component...(i think)
> ...
> tblFeatures (may rename this later...tblConditionActual??? for consistency
> of naming convention)
> fldFeatureID PK
> fldComponentID FK <from tblComponent>...(which component is this record
> applied to)
> fldEndDesc Text (Left, right, middle, userdefined..etc etc)
> (maybe rename the above field to fldConditionLocation for consistency of
> naming convention)
> fldConditionID FK <from tblConditionList>
> fldCondititonVariationID FK <from tblConditionVariation> (will be NULL
> unless it's a variation)
> fldConditionSpecialID FK <from tblConditionSpecial> (will be NULL unless
> it's a "Special")
>
> I think the balance of the schema is perfect and this should be enough to
> get me off the ground....(I think)
> I'll be very happy to hear any other ideas you may have come up with and/or
> comments about this idea, but if I've run out my 'dance card' I think I may
> be able to stumble along by myself now...unless you post back saying I still
> have it all backward
> :-)
>
> Now all I have to do is figuring out all the sql statements to make all this
> work!
>
> Thanks for the help and guidance.
>
> Mark[/color]

I just got busy. I'm writing a new module for time ticket entries and
there is a lot of pressure on me to get it out as quickly as possible
so that 1) the new time tickets can be used and 2) a minimum number of
2006 time tickets will have to be converted to the new format. I will
get back to this thread as soon as I am able. I'm glad you took time
to study what I posted in the meantime. P.S., as Kyle suggested, there
is no charge for verbosity but it tends to get in the way of clear,
concise thinking about the most important points. Substitute my
references to Access with "Your Program." Plus, start at the top of
your schema design and work your way down. You seem to be starting to
grasp the "why" of additional tables so maybe your study of
normalization techniques will make more sense now.

Hope this helps,

James A. Fortune
CDMAPoster@FortuneJames.com

<CDMAPoster@FortuneJames.com> wrote in message
news:1136417790.192498.114130@g44g2000cwa.googlegr oups.com...[color=blue]
> I just got busy. I'm writing a new module for time ticket entries and
> there is a lot of pressure on me to get it out as quickly as possible
> so that 1) the new time tickets can be used and 2) a minimum number of
> 2006 time tickets will have to be converted to the new format. I will
> get back to this thread as soon as I am able. I'm glad you took time
> to study what I posted in the meantime. P.S., as Kyle suggested, there
> is no charge for verbosity but it tends to get in the way of clear,
> concise thinking about the most important points. Substitute my
> references to Access with "Your Program." Plus, start at the top of
> your schema design and work your way down. You seem to be starting to
> grasp the "why" of additional tables so maybe your study of
> normalization techniques will make more sense now.
>
> Hope this helps,
>
> James A. Fortune
> CDMAPoster@FortuneJames.com
>[/color]

Thanks , I'll be glad to see what other thoughts you come up with when you
have time.
Good luck with your new project
Thanks for everything you've done so far.
Mark

Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

re: Database design question

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about