small but complex "DB structure"

B

billyradd

Referring to my previous post "Designing a DB for Tract Home Models w/ Many
Options" I have decided to use Access to accomplish this task.

I work for a manufacturing company that designs and builds tract homes for
many customers. These houses can be designed once and built many times from
that one design. In the past I have only kept track of “Truss†bases; my task
now is to incorporate Floors and Walls into this system and a simple spread
sheet would be far to cumbersome.

My objective is to create a "Base Houses" tracking system that will
calculate possible elevation and option combinations and track such things as
“Designed or not designedâ€, “Status – active or discontinuedâ€, “Design
criteria – building codes and design loads specific to area the home is being
built†and other things such as “Optionsâ€, “Elevationsâ€, “Number of pieces
per base†and “Date designedâ€

Here is my “novice†structure:

tblCustomer / each customer has many models

tblDesignCategories / Floors, Walls or Trusses

tblModels /each model has a Name and a Number and can have many elevations
and many options

tblElevations / Elevation Name and options combinations determine "Base
structure"

tblOptions / Options can be 3 types; (Base) “Standard model w/ no optionsâ€,
(INT.) “options Integrated into the base†or (ADD) “options Added-on to baseâ€

tblStatus / Models are either Active or Discontinued.


Below is a typical "Base Structure" and how I generate it.
______________________________________
(ex. Format) “Bases†(this is the list what I want generate and track)

[Information input by user]
Category = Trusses (T_)
Model = 2600_Hamden (2600)
Elevations = A, B1, B2 and C
Integrated Options = Sloped Ceiling, Morning room
Add-on Options = 3rd Car Garage and Screened in porch

[Information compiled by query or other method]
T_2600_A1 = Base [standard ‘A’ house w/ no options] “1â€
T_2600_A2 = Slope ceiling [integrated option] “2â€
T_2600_A3 = Morning Room [integrated option] “3â€
T_2600_A4 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_B11 = Base option “1â€
T_2600_B12 = Slope ceiling [integrated option] “2â€
T_2600_B13 = Morning Room [integrated option] “3â€
T_2600_B14 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_B21 = Base [standard ‘B1’ house w/ no options] “1â€
T_2600_B22 = Slope ceiling [integrated option] “2â€
T_2600_B23 = Morning Room [integrated option] “3â€
T_2600_B24 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_C1 = Base [standard ‘C’ house w/ no options] “1â€
T_2600_C2 = Slope ceiling [integrated option] “2â€
T_2600_C3 = Morning Room [integrated option] “3â€
T_2600_C4 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_3C = 3rd Car [add on option] “3C†applies to all elevations.
T_2600_SP = Screened porch [add on option] “SP†applies to all elevations.
______________________________________

Without designing it for me, how should I create this monster? How should
the tables relate to each other and what method is best for generating each
models structure?

I realize this may be too much of a request.
…so “Thank you†in advance if you care to tackle this one.
 
B

billyradd

You bunch of Chickens :p

:)


billyradd said:
Referring to my previous post "Designing a DB for Tract Home Models w/ Many
Options" I have decided to use Access to accomplish this task.

I work for a manufacturing company that designs and builds tract homes for
many customers. These houses can be designed once and built many times from
that one design. In the past I have only kept track of “Truss†bases; my task
now is to incorporate Floors and Walls into this system and a simple spread
sheet would be far to cumbersome.

My objective is to create a "Base Houses" tracking system that will
calculate possible elevation and option combinations and track such things as
“Designed or not designedâ€, “Status – active or discontinuedâ€, “Design
criteria – building codes and design loads specific to area the home is being
built†and other things such as “Optionsâ€, “Elevationsâ€, “Number of pieces
per base†and “Date designedâ€

Here is my “novice†structure:

tblCustomer / each customer has many models

tblDesignCategories / Floors, Walls or Trusses

tblModels /each model has a Name and a Number and can have many elevations
and many options

tblElevations / Elevation Name and options combinations determine "Base
structure"

tblOptions / Options can be 3 types; (Base) “Standard model w/ no optionsâ€,
(INT.) “options Integrated into the base†or (ADD) “options Added-on to baseâ€

tblStatus / Models are either Active or Discontinued.


Below is a typical "Base Structure" and how I generate it.
______________________________________
(ex. Format) “Bases†(this is the list what I want generate and track)

[Information input by user]
Category = Trusses (T_)
Model = 2600_Hamden (2600)
Elevations = A, B1, B2 and C
Integrated Options = Sloped Ceiling, Morning room
Add-on Options = 3rd Car Garage and Screened in porch

[Information compiled by query or other method]
T_2600_A1 = Base [standard ‘A’ house w/ no options] “1â€
T_2600_A2 = Slope ceiling [integrated option] “2â€
T_2600_A3 = Morning Room [integrated option] “3â€
T_2600_A4 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_B11 = Base option “1â€
T_2600_B12 = Slope ceiling [integrated option] “2â€
T_2600_B13 = Morning Room [integrated option] “3â€
T_2600_B14 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_B21 = Base [standard ‘B1’ house w/ no options] “1â€
T_2600_B22 = Slope ceiling [integrated option] “2â€
T_2600_B23 = Morning Room [integrated option] “3â€
T_2600_B24 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_C1 = Base [standard ‘C’ house w/ no options] “1â€
T_2600_C2 = Slope ceiling [integrated option] “2â€
T_2600_C3 = Morning Room [integrated option] “3â€
T_2600_C4 = Slope ceiling and Morning room [integrated option] “4â€

T_2600_3C = 3rd Car [add on option] “3C†applies to all elevations.
T_2600_SP = Screened porch [add on option] “SP†applies to all elevations.
______________________________________

Without designing it for me, how should I create this monster? How should
the tables relate to each other and what method is best for generating each
models structure?

I realize this may be too much of a request.
…so “Thank you†in advance if you care to tackle this one.
Click to expand...
 
B

billyradd

Well I'd certainly hope so :) ... Oh, there it is! that was fast!

I though that might get some kind of a response ;)


(This place is a wonderful service and Thanks! To everyone who take their
time to answer some rather repetitive and ridiculous question. I admire and
appreciate it very much.)
 
B

billyradd

Had you actually posted something like:

"Your request exceeds what we can and should provide here at
(Oh-Mighty-Microsoft-Discussion-Groups-For-You-Free-Loading-Worthless-People)
I would highly recommend hiring someone with the appropriate training to help
you accomplish you task, here are a few recomendation to help you find
someone..."

Then If I was to reply with something like:

"Well, you bunch of worthess chickens couldn't firgure out my request anyway
because it is too complex for you to comprehend. I Want my connection fee for
connecting to this service back!"

Then it would be understandable for you to reply back with something like
what you did.

All I playfully [ hence the ":p" ] said was, and simply for the fact that
NO_ONE repleid with ANY_THING, "you bunch of chickens :p" You dont think i
though it might be to much to ask in the first place? check the end of my
original post.
 
B

BruceM

Yes, this is quite a bit of database here. Other comments inline.

billyradd said:
Referring to my previous post "Designing a DB for Tract Home Models w/
Many
Options" I have decided to use Access to accomplish this task.

I work for a manufacturing company that designs and builds tract homes for
many customers. These houses can be designed once and built many times
from
that one design. In the past I have only kept track of "Truss" bases; my
task
now is to incorporate Floors and Walls into this system and a simple
spread
sheet would be far to cumbersome.

My objective is to create a "Base Houses" tracking system that will
calculate possible elevation and option combinations and track such things
as
"Designed or not designed", "Status - active or discontinued", "Design
criteria - building codes and design loads specific to area the home is
being
built" and other things such as "Options", "Elevations", "Number of pieces
per base" and "Date designed"

Here is my "novice" structure:

tblCustomer / each customer has many models
Click to expand...

This is a bit puzzling on the face of it. Are these customers people other
than the ones who buy the homes as place to live? How is it that they may
have many models.
tblDesignCategories / Floors, Walls or Trusses
Click to expand...

How is this an "or" list? Remember that we don't necessarily understand the
business situation. Is the idea that one of these choices will lead to a
list of models? If so, tblModels would be related to this table. Or maybe
tblModels would have a category field. That would probably be a practical
approach.
tblModels /each model has a Name and a Number and can have many elevations
and many options
Click to expand...

It seems to me the elevations don't really come into effect until the
options are selected. The integrated options and the add-on options would
affect some if not all of the elevations (asuming that by "elevation" you
mean a view of the house from the outside). With two integrated options and
two add-on options there are 12 combinations, by my count, but then some
options don't affect all of the elevations. I can see the model table
storing just the base model information.
tblElevations / Elevation Name and options combinations determine "Base
structure"

tblOptions / Options can be 3 types; (Base) "Standard model w/ no options",
(INT.) "options Integrated into the base" or (ADD) "options Added-on to
base"
Click to expand...

"Standard model w/ no options" is the information stored in tblModels. The
options are the two integrated options plus the two add-on options. The
choice of options would affect the elevations, so I would think the options
and elevations would be part of the Options table. If you are including
actual graphics they should be in linked files, by the way, rather than
stored in the database. Anyhow, it could be that another model has a porch
option, but the elevations would be different from model to model.
tblStatus / Models are either Active or Discontinued.
Click to expand...
One yes/no field for Active in the Models table would take care of this. Or
maybe the base model is Active and some of the options are discontinued. Or
both. In any case, I don't see the point of a table for this.
Below is a typical "Base Structure" and how I generate it.
______________________________________
(ex. Format) "Bases" (this is the list what I want generate and track)

[Information input by user]
Category = Trusses (T_)
Model = 2600_Hamden (2600)
Elevations = A, B1, B2 and C
Integrated Options = Sloped Ceiling, Morning room
Add-on Options = 3rd Car Garage and Screened in porch

[Information compiled by query or other method]
T_2600_A1 = Base [standard 'A' house w/ no options] "1"
T_2600_A2 = Slope ceiling [integrated option] "2"
T_2600_A3 = Morning Room [integrated option] "3"
T_2600_A4 = Slope ceiling and Morning room [integrated option] "4"

T_2600_B11 = Base option "1"
T_2600_B12 = Slope ceiling [integrated option] "2"
T_2600_B13 = Morning Room [integrated option] "3"
T_2600_B14 = Slope ceiling and Morning room [integrated option] "4"

T_2600_B21 = Base [standard 'B1' house w/ no options] "1"
T_2600_B22 = Slope ceiling [integrated option] "2"
T_2600_B23 = Morning Room [integrated option] "3"
T_2600_B24 = Slope ceiling and Morning room [integrated option] "4"

T_2600_C1 = Base [standard 'C' house w/ no options] "1"
T_2600_C2 = Slope ceiling [integrated option] "2"
T_2600_C3 = Morning Room [integrated option] "3"
T_2600_C4 = Slope ceiling and Morning room [integrated option] "4"

T_2600_3C = 3rd Car [add on option] "3C" applies to all elevations.
T_2600_SP = Screened porch [add on option] "SP" applies to all elevations.
______________________________________

Without designing it for me, how should I create this monster? How should
the tables relate to each other and what method is best for generating
each
models structure?

I realize this may be too much of a request.
.so "Thank you" in advance if you care to tackle this one.
Click to expand...
How the tables relate would depend on the issues I have raised, plus other
considerations I have no doubt omitted. A clearer understanding of the
business situation would be the biggest factor in answering your questions.
 
B

billyradd

Hey thanks for tackling this. You are very close... my follow-ups in-line as
well.

BruceM said:
Yes, this is quite a bit of database here. Other comments inline.



This is a bit puzzling on the face of it. Are these customers people other
than the ones who buy the homes as place to live? How is it that they may
have many models.
Click to expand...

Our customers come to us to build the houses they design and sell. We
produce the Floor package consisting of I-Joists and beams for the floors, We
build wall panels out of 2x4's (prefab walls) and we also build the roof
trusses.

The customer may have several Home Models
Each model can have from 1 or more "Elevations" (mainly a change in outside
appearance such as a front porch or a different look to the roof. So some
â€Elevations†will affect the roof while not affecting the floor and
vise-versa and add the walls…)
How is this an "or" list? Remember that we don't necessarily understand the
business situation. Is the idea that one of these choices will lead to a
list of models? If so, tblModels would be related to this table. Or maybe
tblModels would have a category field. That would probably be a practical
approach.
Click to expand...

These are the 3 products we supply from our location. Floors (i-Joist floor
systems) Walls (Premanufactured Wall Panels) And Trusses (Structural Roof
truss systems)... all of which are "Designed" by our "designers" The sales
reps bring in the customers with there inventory of homes that they sell. We
design the floors walls and trusses and give this customer a bid for each
home model with prices for elevations and options that go along with it. All
of these factors change pretty readily too so it must be flexible in format.
Like adding or subtracting elevations or options that don’t sell or options
that do sell a lot can become a standard option that comes with the “Base
House†( The “base house†is the model in each of it’s elevation with no
options included, like: The model is, lets say, the 1809_Harlet and it has 4
elevations, ‘A’, ‘B1’, ‘B2’ & ‘C’ There would be a “Base House†For each
elevation†like this: 1809_A1 = Elevation ‘A’ “Base†1809_B1 = Elevation ‘B’
“Base†and so on… then, as each option is applied the number increments by
number or can have a user input value of any numbers that do not repeat. Some
option cancel out other options as well, thickening the plot.)
It seems to me the elevations don't really come into effect until the
options are selected. The integrated options and the add-on options would
affect some if not all of the elevations (asuming that by "elevation" you
mean a view of the house from the outside). With two integrated options and
two add-on options there are 12 combinations, by my count, but then some
options don't affect all of the elevations. I can see the model table
storing just the base model information.
Click to expand...

(See above about elevations.)
tblElevations / Elevation Name and options combinations determine "Base
structure"

tblOptions / Options can be 3 types; (Base) "Standard model w/ no options",
(INT.) "options Integrated into the base" or (ADD) "options Added-on to
base"
Click to expand...

"Standard model w/ no options" is the information stored in tblModels. The
options are the two integrated options plus the two add-on options. The
choice of options would affect the elevations, so I would think the options
and elevations would be part of the Options table. If you are including
actual graphics they should be in linked files, by the way, rather than
stored in the database. Anyhow, it could be that another model has a porch
option, but the elevations would be different from model to model.
tblStatus / Models are either Active or Discontinued.
Click to expand...
One yes/no field for Active in the Models table would take care of this. Or
maybe the base model is Active and some of the options are discontinued. Or
both. In any case, I don't see the point of a table for this.
Below is a typical "Base Structure" and how I generate it.
______________________________________
(ex. Format) "Bases" (this is the list what I want generate and track)

[Information input by user]
Category = Trusses (T_)
Model = 2600_Hamden (2600)
Elevations = A, B1, B2 and C
Integrated Options = Sloped Ceiling, Morning room
Add-on Options = 3rd Car Garage and Screened in porch

[Information compiled by query or other method]
T_2600_A1 = Base [standard 'A' house w/ no options] "1"
T_2600_A2 = Slope ceiling [integrated option] "2"
T_2600_A3 = Morning Room [integrated option] "3"
T_2600_A4 = Slope ceiling and Morning room [integrated option] "4"

T_2600_B11 = Base option "1"
T_2600_B12 = Slope ceiling [integrated option] "2"
T_2600_B13 = Morning Room [integrated option] "3"
T_2600_B14 = Slope ceiling and Morning room [integrated option] "4"

T_2600_B21 = Base [standard 'B1' house w/ no options] "1"
T_2600_B22 = Slope ceiling [integrated option] "2"
T_2600_B23 = Morning Room [integrated option] "3"
T_2600_B24 = Slope ceiling and Morning room [integrated option] "4"

T_2600_C1 = Base [standard 'C' house w/ no options] "1"
T_2600_C2 = Slope ceiling [integrated option] "2"
T_2600_C3 = Morning Room [integrated option] "3"
T_2600_C4 = Slope ceiling and Morning room [integrated option] "4"

T_2600_3C = 3rd Car [add on option] "3C" applies to all elevations.
T_2600_SP = Screened porch [add on option] "SP" applies to all elevations.
______________________________________

Without designing it for me, how should I create this monster? How should
the tables relate to each other and what method is best for generating
each
models structure?

I realize this may be too much of a request.
.so "Thank you" in advance if you care to tackle this one.
Click to expand...
How the tables relate would depend on the issues I have raised, plus other
considerations I have no doubt omitted. A clearer understanding of the
business situation would be the biggest factor in answering your questions.
Click to expand...

Thank you Thank you Thank you
 
B

BruceM

billyradd said:
Hey thanks for tackling this. You are very close... my follow-ups in-line
as
well.



Our customers come to us to build the houses they design and sell. We
produce the Floor package consisting of I-Joists and beams for the floors,
We
build wall panels out of 2x4's (prefab walls) and we also build the roof
trusses.
Click to expand...

I think I see. You are in effect a contractor, but you are building in a
factory rather than stick building on site. Your customers are developers
who offer an assortment of houses to individual customers. Is it something
like that? Or do you have standard models that may be used by several
developers?
The customer may have several Home Models
Each model can have from 1 or more "Elevations" (mainly a change in
outside
appearance such as a front porch or a different look to the roof. So some
"Elevations" will affect the roof while not affecting the floor and
vise-versa and add the walls.)


These are the 3 products we supply from our location. Floors (i-Joist
floor
systems) Walls (Premanufactured Wall Panels) And Trusses (Structural Roof
truss systems)... all of which are "Designed" by our "designers" The sales
reps bring in the customers with there inventory of homes that they sell.
We
design the floors walls and trusses and give this customer a bid for each
home model with prices for elevations and options that go along with it.
All
of these factors change pretty readily too so it must be flexible in
format.
Like adding or subtracting elevations or options that don't sell or
options
that do sell a lot can become a standard option that comes with the "Base
House" ( The "base house" is the model in each of it's elevation with no
options included, like: The model is, lets say, the 1809_Harlet and it has
4
elevations, 'A', 'B1', 'B2' & 'C' There would be a "Base House" For each
elevation" like this: 1809_A1 = Elevation 'A' "Base" 1809_B1 = Elevation
'B'
"Base" and so on. then, as each option is applied the number increments by
number or can have a user input value of any numbers that do not repeat.
Some
option cancel out other options as well, thickening the plot.)
Click to expand...

OK, I have a few new questions and comments. What is in
tblDesignCategories? Just those three items? Unless it is a lookup table,
I don't see its point.
Are the four elevations front, left, right, and back elevations for a single
house, or are these front elevations for four different subtypes, or what
exactly? I still can't make out what you mean by "1809_Harlet ... has 4
elevations."
A separate table each for these three items seems to make sense. Are
Floors, Walls, and Trusses interchangeable in some cases between models (a
standard 12' wall panel or something like that)? If so, each model may have
many Wall units, and each Wall unit may be associated with many models.
That is a many-to-many relationship, for which a junction table is needed
between Model and Wall. Same goes for Floor and Truss.
I can't make out what you mean by "as each option is applied the number
increments by number or can have a user input value of any numbers that do
not repeat". I think I understand this to mean in part that a user can only
select one morning room or whatever, but I really don't follow "the number
increments by number."
(See above about elevations.)
Click to expand...

The elevations stuff seems to be central to understanding this for me, as
explained above.
tblElevations / Elevation Name and options combinations determine "Base
structure"

tblOptions / Options can be 3 types; (Base) "Standard model w/ no
options",
(INT.) "options Integrated into the base" or (ADD) "options Added-on to
base"
Click to expand...

"Standard model w/ no options" is the information stored in tblModels.
The
options are the two integrated options plus the two add-on options. The
choice of options would affect the elevations, so I would think the
options
and elevations would be part of the Options table. If you are including
actual graphics they should be in linked files, by the way, rather than
stored in the database. Anyhow, it could be that another model has a
porch
option, but the elevations would be different from model to model.
tblStatus / Models are either Active or Discontinued.
Click to expand...
One yes/no field for Active in the Models table would take care of this.
Or
maybe the base model is Active and some of the options are discontinued.
Or
both. In any case, I don't see the point of a table for this.
Below is a typical "Base Structure" and how I generate it.
______________________________________
(ex. Format) "Bases" (this is the list what I want generate and track)

[Information input by user]
Category = Trusses (T_)
Model = 2600_Hamden (2600)
Elevations = A, B1, B2 and C
Integrated Options = Sloped Ceiling, Morning room
Add-on Options = 3rd Car Garage and Screened in porch

[Information compiled by query or other method]
T_2600_A1 = Base [standard 'A' house w/ no options] "1"
T_2600_A2 = Slope ceiling [integrated option] "2"
T_2600_A3 = Morning Room [integrated option] "3"
T_2600_A4 = Slope ceiling and Morning room [integrated option] "4"

T_2600_B11 = Base option "1"
T_2600_B12 = Slope ceiling [integrated option] "2"
T_2600_B13 = Morning Room [integrated option] "3"
T_2600_B14 = Slope ceiling and Morning room [integrated option] "4"

T_2600_B21 = Base [standard 'B1' house w/ no options] "1"
T_2600_B22 = Slope ceiling [integrated option] "2"
T_2600_B23 = Morning Room [integrated option] "3"
T_2600_B24 = Slope ceiling and Morning room [integrated option] "4"

T_2600_C1 = Base [standard 'C' house w/ no options] "1"
T_2600_C2 = Slope ceiling [integrated option] "2"
T_2600_C3 = Morning Room [integrated option] "3"
T_2600_C4 = Slope ceiling and Morning room [integrated option] "4"

T_2600_3C = 3rd Car [add on option] "3C" applies to all elevations.
T_2600_SP = Screened porch [add on option] "SP" applies to all
elevations.
______________________________________

Without designing it for me, how should I create this monster? How
should
the tables relate to each other and what method is best for generating
each
models structure?

I realize this may be too much of a request.
.so "Thank you" in advance if you care to tackle this one.
Click to expand...
How the tables relate would depend on the issues I have raised, plus
other
considerations I have no doubt omitted. A clearer understanding of the
business situation would be the biggest factor in answering your
questions.
Click to expand...

Thank you Thank you Thank you
Click to expand...
 
Top