Database design question
MP
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
pietl...@hotmail.com
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.
CDMAP...@fortunejames.com
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
CDMAP...@FortuneJames.com
MP
news:1135225299.8...@g49g2000cwa.googlegroups.com...
Hi Piet,
Thanks for the response.
If I may ask a clarifying question or two...<g>
> I would store the values separately -
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
> "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.
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
MP
news:1135226321.4...@g49g2000cwa.googlegroups.com...
> MP wrote:
> > 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)
> > :-)
> >
> You might want to check out non-relational databases first to see if
> they offer any advantages over relational databases for your situation.
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.
> If not, many people here can help you come up with a plan to create and
> handle the data relationships.
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
CDMAP...@fortunejames.com
> <CDMAP...@FortuneJames.com> wrote in message
> news:1135226321.4...@g49g2000cwa.googlegroups.com...
> > MP wrote:
> > > 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)
> > > :-)
> > >
>
> > You might want to check out non-relational databases first to see if
> > they offer any advantages over relational databases for your situation.
>
> 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.
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.
> > If not, many people here can help you come up with a plan to create and
> > handle the data relationships.
>
> 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
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
CDMAP...@FortuneJames.com
MP
news:1135277208.2...@z14g2000cwz.googlegroups.com...
> MP wrote:
> > <CDMAP...@FortuneJames.com> wrote in message
> > news:1135226321.4...@g49g2000cwa.googlegroups.com...
> > > MP wrote:
> > > > Greets,
> > > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> > > >
> > > > beginning learner, first database, planning stages
> > > >
>
> I meant post-relational. Here's a link:
>
> http://www.intersystems.com/healthcare/healthcare.html
Thanks for the link I'll check that out...have not heard the term before
'Post-Relational'
>
> When I saw the possibility of components with a large relational map it
> made me think that it might hold some promise.
>
> > > If not, many people here can help you come up with a plan to create
and
> > > handle the data relationships.
> >
> Maybe your problem is simple.
Absolutely it is!!! I can sum it up in one word. Ignorance.(mine)
:-)
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.
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
CDMAP...@fortunejames.com
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
CDMAP...@FortuneJames.com
MP
news:1135296403.0...@g43g2000cwa.googlegroups.com...
> Show your schema! I do not fear it.
<yet>
:-)
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
> CDMAP...@FortuneJames.com
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
CDMAP...@fortunejames.com
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
CDMAP...@FortuneJames.com
Peace and goodwill.
MP
news:1135367453.0...@f14g2000cwb.googlegroups.com...
> MP wrote:
> > <CDMAP...@FortuneJames.com> wrote in message
> > news:1135296403.0...@g43g2000cwa.googlegroups.com...
> >
> > > Show your schema! I do not fear it.
> > Ok, here goes...
> >
> > The main objects I see in my overview are:
> > 1) Job
> > 2) Component
> > 3) Client
> 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
> CDMAP...@FortuneJames.com
>
> Peace and goodwill.
Sounds great. I really appreciate your looking at this.
Hope you had a good weekend.
Look forward to further conversations.
:-)
Mark
MP
news:1135367453.0...@f14g2000cwb.googlegroups.com...
> MP wrote:
> > <CDMAP...@FortuneJames.com> wrote in message
> > news:1135296403.0...@g43g2000cwa.googlegroups.com...
> >
> > > Show your schema! I do not fear it.
> >
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
CDMAP...@fortunejames.com
> Greets,
> context: vb6/ado/.mdb/jet 4.0 (no access)/sql
>
> beginning learner, first database, planning stages
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
CDMAP...@FortuneJames.com
MP
news:1135735646.9...@g49g2000cwa.googlegroups.com...
> MP wrote:
> > Greets,
> > context: vb6/ado/.mdb/jet 4.0 (no access)/sql
> >
> > beginning learner, first database, planning stages
>
> 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:
>
Thank you so much for looking at this.
I will study your reply and respond back in detail asap.
Thanks again,
Mark
MP
news:1135735646.9...@g49g2000cwa.googlegroups.com...
> MP wrote:
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.
>To give you an idea of what I'm
> looking at, think of La1Ra looking more like:
>
----------------------------------------------------------------------------
-
> tblFeatures
Yes
--------------------------------
> fldFID PK
Yes, meaningless Unique Long or GUID Key
--------------------------------
> fldFeatureGroupID Long
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?
--------------------------------
> fldJobID Long
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.
--------------------------------
> fldEndDesc Text
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)
--------------------------------
> 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).
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
> feature. This is just a small example of where I'm going since this
> idea needs to be checked against your entire schema.
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
> 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.
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
> 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
not sure where you're going with this one...is this the variations per given
feature? like La1 versus La ?
> fldConditionID PK
> fldCondition Text
----------------------------------------------------------------------------
-
> tblFeatures
I'm still not clear on this one...see comments above
> fldFID PK
Yes, meaningless Unique Long or GUID Key
> fldFeatureGroupID Long
> fldJobID Long
> fldEndDesc Text
> fldConditionID Long
> fldVariationNumber Long
> ...
>
----------------------------------------------------------------------------
-
> tblJob
Yes
> fldJobID PK
Yes
> fldJobNumber Long
Yes
> fldJobName Text
Yes
> fldClientID Long
Yes
> ...
----------------------------------------------------------------------------
-
> tblClient
Yes
> fldClientID PK
Yes
> fldClientName Text
Yes
> fldClientAddress Text
Yes
> fldClientCity Text
Yes
----------------------------------------------------------------------------
-
> tblClientStandards
Yes
> CSID PK
Yes
> fldClientID FK
Yes
> fldStandardName Text
Yes
----------------------------------------------------------------------------
-
> tblComponent
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
--------------------------------
> fldComponentID PK
Yes
--------------------------------
> fldJobID FK
Yes
--------------------------------
> fldComponentSubTypeID FK
Yes
--------------------------------
> fldFamilyNumber FK
not sure but wouldn't this be fldFamilyID from tblFamilies below?
--------------------------------
> fldComponentName Text
yes, this is the final name derived/calculated/stored data after all
components are entered/sorted/and named
--------------------------------
> fldComponentLength Double
Yes
--------------------------------
> fldComponentUnits Text
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
>
----------------------------------------------------------------------------
-
> tblComponentSubType
Yes, I see the need for a table separating the 3 subtypes of components
--------------------------------
> fldCompSubTypeID PK
Yes
--------------------------------
> fldCompSubTypeName Text
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)
----------------------------------------------------------------------------
-
> tblFamilies
----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
--------------------------------
> fldFamilyID PK
yes, I see the need for a PK ID field
--------------------------------
> fldCompSubTypeID FK
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? :-)
-------------------------------
> fldFamilyNumber Long
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)
--------------------------------
> fldFamilyName Text
Yes, this is my primary data(fact about component)
this is one record in the list of legal names right?
----------------------------------------------------------------------------
-
> tblCompA
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?
--------------------------------
> fldCAID PK
Yes
--------------------------------
> fldComponentSubTypeID FK
This seems like the samething as fldCAID above???
--------------------------------
> fldFamilyNumber FK
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
--------------------------------
> fldShape Text
derived from fldFamilyNumber above...I would think this would be in
tblFamilyName...ie what shape does family"A" refer to on Job 12345
--------------------------------
> fldCompASpecificField Text
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?
>
> 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, ...
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
> 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,
>
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>)
> James A. Fortune
> CDMAP...@FortuneJames.com
>
CDMAP...@fortunejames.com
> 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>)
>
>
> > James A. Fortune
> > CDMAP...@FortuneJames.com
> >
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
CDMAP...@FortuneJames.com