473,695 Members | 2,730 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Database design question

MP
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
"tblCompone nt"
One of the data items(fields) is an alphanumeric identifier("fld Feature").
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
"tblCompone nt" 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, "fldFeature ID" as Foreign Key in the field
"fldFeature ID" in table "tblCompone nt"
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
Dec 22 '05 #1
29 3568
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.

Dec 22 '05 #2
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)
:-)

Background info:
Part of my project involves storing data about some objects in table
"tblCompone nt"
One of the data items(fields) is an alphanumeric identifier("fld Feature").
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
"tblCompone nt" 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, "fldFeature ID" as Foreign Key in the field
"fldFeature ID" in table "tblCompone nt"
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


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
CD********@Fort uneJames.com

Dec 22 '05 #3
MP
<pi********@hot mail.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .
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.
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 "tblCompone nt"?
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"
fldLeftVariatio n, value: "1" (or null if no variation)
fldRightFeature , value "b"
fldRightVariati on, 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 fldLeftVariatio n = Null
And fldRightFeature = "a"
And fldRightVariati on = Null

is that the idea?

Thanks for your input
Mark
Dec 22 '05 #4
MP
<CD********@For tuneJames.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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
Dec 22 '05 #5
MP wrote:
<CD********@For tuneJames.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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
CD********@Fort uneJames.com

Dec 22 '05 #6
MP
<CD********@For tuneJames.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
MP wrote:
<CD********@For tuneJames.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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
Dec 22 '05 #7
MP wrote:
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


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
CD********@Fort uneJames.com

Dec 23 '05 #8
MP
<CD********@For tuneJames.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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
CD********@Fort uneJames.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
fldComponentSub TypeID(PK from tblComponentSub Type - <see below>), and many
other data fields)
tblClient
(fields PK fldClientID, fldClientName, fldClientAddres s,
fldClientStanda rds, 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?)
tblComponentSub Type (whose PK becomes a FK in tblComponent)
(fields: PK fldCompSubTypeI D, fldCompSubTypeN ame,
fldCompSubTypeU niqueValueTable ?)
I guess(not sure) that I need 3 "subtables" to hold the unique data fields
for each ComponentSubtyp e???
if that's correct the above mentioned fldCompTypeUniq ueValueTable 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 ComponentSubTyp e 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 ComponentSubTyp eA.(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
"ComponentN ame"
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), fldFamilyDescri ption (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.fldFam ilyID = tblFamily.fldFa milyID _
For each fldLength in tblLengths
AND tblCompA.fldLen gth = fldLength
And tblCompA.fldFea ture = "straight-both-ends"(no adjustment
for end features)
THEN tblCompA.fldCub icFeet = (tblFamily.fldA reaDouble ) 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
Dec 23 '05 #9
MP wrote:
<CD********@For tuneJames.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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
CD********@Fort uneJames.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
fldComponentSub TypeID(PK from tblComponentSub Type - <see below>), and many
other data fields)
tblClient
(fields PK fldClientID, fldClientName, fldClientAddres s,
fldClientStanda rds, 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?)
tblComponentSub Type (whose PK becomes a FK in tblComponent)
(fields: PK fldCompSubTypeI D, fldCompSubTypeN ame,
fldCompSubTypeU niqueValueTable ?)
I guess(not sure) that I need 3 "subtables" to hold the unique data fields
for each ComponentSubtyp e???
if that's correct the above mentioned fldCompTypeUniq ueValueTable 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 ComponentSubTyp e 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 ComponentSubTyp eA.(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
,,,


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
CD********@Fort uneJames.com

Peace and goodwill.

Dec 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1516
by: Lefevre, Steven | last post by:
Hey folks - Thanks to everyone who gave input to my concerns. Of course, we don't intend to have the mysql port open to the world. We will have Apache/PHP connect on a unix socket, or to another machine with a cross-cable on non-routeable IPs. But now I have another question. We are working on a web database to allow our suppliers to log on and submit information that they would otherwise fax, email, or phone to us. It would reduce...
5
674
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of generic design patterns that can be used and shared amongst many sub-schemas. For example, the grouping of entities. I may have the following tables: employee, product and client. These tables have no direct relationship with each other. But...
3
1865
by: reageer | last post by:
Hi all, I have a design question: I have a bunch of users (name, address, zip, etc.). They are assigned a card with a specific id. The only thing unique is this card id, or probably the combination of all other user fields. So it's seductive to use the card id as the primary key. This card allows access to certain places and all access is logged.
12
7004
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a foreign Key relationship or does this always indicate some sort of underlying design flaw. Something that requires a re evaluation of the problem domain? The reason I ask is because in our application, the user can perform x
1
7129
by: arrival123 | last post by:
Hello, I'm currently trying to decide on a database design for tags in my web 2.0 application. The problem I'm facing is that I have 3 separate tables i.e. cars, planes, and schools. All three tables need to interact with the tags, so there will only be one universal set of tags for the three tables. I read a lot about tags and the best articles I found were: Road to Web 2.0 ( http://wyome.com/docs/Road_to_Web_2.0:_The_Database_Design )...
10
5809
by: ARC | last post by:
Hello all, General question for back-end database that has numerous date fields where the database will be used in regions that put the month first, and regions that do not. Should I save a date format in the table design, such as: mm/dd/yyyy? What I've done for years is to store the date format in date fields, then on the forms, based on their region, I would set the date formats on form_load
4
2917
by: dgleeson3 | last post by:
Hello all I am creating a VB.Net distributed SQL server 2005 application. Each computer in the system has a database with a table of users and their telephone numbers. Each computer has a unique 4 digit identifying code. The central server runs an application which reads the database table on each computer.
0
1344
by: David | last post by:
Hi list. I have a few database-related questions. These aren't Python-specific questions, but some of my apps which use (or will use) these tables are in Python :-) Let me know if I should ask this on a different list. Question 1: Storing app defaults. If you have a table like this:
10
3365
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Not sure if I quite follow that. 1. Data encrypted by AES key 2. AES key encrypted with Asymmetric public key (?)
0
8555
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8817
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7651
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4336
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4571
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2258
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.