a little design help thanks

[Bert]

I am working on a database to track PO's and there associated budgets. My
company has 100 locations. Each of these locations budgets are broken down
by categories (we have 8 ), period (1-12) and year. Some quick math....that
accounts for 9600 separate budget items per year...Most items are budgeted
this way, the other way is to budget the PO to a project number. Since projects
span multiple offices the project is given its own budget. Project budgets
are broken down the same way except it goes project number, categories period
then year.

Thought I had it figured out with 5 tables (for the budget portion)
1. categories - holds category information
2. Period - holds period information
3. locations - holds location information
4. BudgetYear - holds year information

These above four tables are in a one to many relationship to the budgets
table, the budget table contains the following fields
1. PeriodID - primary key from period
2. CategoryID -primary key from categories
3. YearID - primary key from budgetyear
4. LocationID primary key form locations table
5. BudgetAmount - The dollar amount for the budget
6. Budget ID - auto generated ID.

When a PO is associate to a budget (according to the users selection of categories
year and period) the BudgetID form the budget table is placed into the PO
table to link them

Now.....what about my projects?

Projects associate to budgets just like locations. A budget is either associated
with a location or project...I have thought of the following...

1. Rename locationID to locationID\ProjectID....if the budget is for location
enter locationID and for project enter projectID...I don' like this Idea.
too much chance of having duplicate locationID and Project ID

2. Add a column to the budget table called ProjectID and mark as 0 for all
budgets associated with Locations, I would also have to mark locationID as
0 for all project PO's

3. Use subset tables, I read a little about these in a book. I guess I
would have a LOCATIONBUDGET table and a PROJECTBUDGET table the would contain
the locationID and the projectID respectively and the BUDGETID form the budget
table. they would be linked in a one-to-one relationship to the budgets
table.....Have never seen this before and it looks as If I would have a ton
of joins in my queries.

4. Create an identical table to Budgets and call it Projectbudgets....This
would work but I place the BudgetID (from either table) how do I know what
table it relates to also it would be more difficult to perform aggregate
functions. For example if I wanted to see the total $$ spent for period
on, including both project and regular.


Don't really know what direction to go..any direction would help. Some tips...a
web link a recommendation for a book

thanks
Bert

[mark]

Bert,

Idea 1 sounds right - except for the name. I don't what would be best for
that. Something generic alike ParentID. And then add a field for parent
type. That way you can add more types in the future. And you wouldn't have
conflicts with keys. If you want to create a report that includes budgets
from both Projects and locations then use a Union like (psuedo code):

"SELECT FROM BUDGET, PROJECT WHERE BUDGET TYPE IS PROJECT AND BUDGET's PROJECT
ID EQUALS PROJECT's ID
UNION
SELECT FROM BUDGET, LOCATION WHERE BUDGET TYPE IS LOCATION AND BUDGET's LOCATION
ID EQUALS LOCATION's ID".

This is a quick read. I would really need to do a more detailed analysis.
I would say hire a short term consultant to help with analysis/design (and
give you some OJT). This would be better than getting a book (If they are
good) and hoping you did it right.

Mark

"Bert" <bertcord@corderman.com> wrote:
>
>I am working on a database to track PO's and there associated budgets.
My
>company has 100 locations. Each of these locations budgets are broken down
> by categories (we have 8 ), period (1-12) and year. Some quick math....that
>accounts for 9600 separate budget items per year...Most items are budgeted
>this way, the other way is to budget the PO to a project number. Since
projects
>span multiple offices the project is given its own budget. Project budgets
>are broken down the same way except it goes project number, categories period
>then year.
>
>Thought I had it figured out with 5 tables (for the budget portion)
>1. categories - holds category information
>2. Period - holds period information
>3. locations - holds location information
>4. BudgetYear - holds year information
>
>These above four tables are in a one to many relationship to the budgets
>table, the budget table contains the following fields
>1. PeriodID - primary key from period
>2. CategoryID -primary key from categories
>3. YearID - primary key from budgetyear
>4. LocationID primary key form locations table
>5. BudgetAmount - The dollar amount for the budget
>6. Budget ID - auto generated ID.
>
>When a PO is associate to a budget (according to the users selection of
categories
>year and period) the BudgetID form the budget table is placed into the PO
>table to link them
>
>Now.....what about my projects?
>
>Projects associate to budgets just like locations. A budget is either
associated
>with a location or project...I have thought of the following...
>
>1. Rename locationID to locationID\ProjectID....if the budget is for location
>enter locationID and for project enter projectID...I don' like this Idea.
>too much chance of having duplicate locationID and Project ID
>
>2. Add a column to the budget table called ProjectID and mark as 0 for
all
>budgets associated with Locations, I would also have to mark locationID
as
>0 for all project PO's
>
>3. Use subset tables, I read a little about these in a book. I guess I
>would have a LOCATIONBUDGET table and a PROJECTBUDGET table the would contain
>the locationID and the projectID respectively and the BUDGETID form the
budget
>table. they would be linked in a one-to-one relationship to the budgets
>table.....Have never seen this before and it looks as If I would have a
ton
>of joins in my queries.
>
>4. Create an identical table to Budgets and call it Projectbudgets....This
>would work but I place the BudgetID (from either table) how do I know what
>table it relates to also it would be more difficult to perform aggregate
>functions. For example if I wanted to see the total $$ spent for period
>on, including both project and regular.
>
>
>Don't really know what direction to go..any direction would help. Some tips...a
>web link a recommendation for a book
>
>thanks
>Bert

[bert]

thanks mark....
that set me on the right direction...I would love to have someone come in
to help...to bad my compnay will not pay for it. Some how I got the complete
job. DAtabse and the web app....hey its good experiance

thanks again
bert

"mark" <mnuttall@nospam.com> wrote:
>
>Bert,
>
>Idea 1 sounds right - except for the name. I don't what would be best
for
>that. Something generic alike ParentID. And then add a field for parent
>type. That way you can add more types in the future. And you wouldn't
have
>conflicts with keys. If you want to create a report that includes budgets
>from both Projects and locations then use a Union like (psuedo code):
>
>"SELECT FROM BUDGET, PROJECT WHERE BUDGET TYPE IS PROJECT AND BUDGET's PROJECT
>ID EQUALS PROJECT's ID
>UNION
>SELECT FROM BUDGET, LOCATION WHERE BUDGET TYPE IS LOCATION AND BUDGET's
LOCATION
>ID EQUALS LOCATION's ID".
>
>This is a quick read. I would really need to do a more detailed analysis.
> I would say hire a short term consultant to help with analysis/design (and
>give you some OJT). This would be better than getting a book (If they are
>good) and hoping you did it right.
>
>Mark
>
>"Bert" <bertcord@corderman.com> wrote:
>>
>>I am working on a database to track PO's and there associated budgets.

>My
>>company has 100 locations. Each of these locations budgets are broken
down
>> by categories (we have 8 ), period (1-12) and year. Some quick math....that
>>accounts for 9600 separate budget items per year...Most items are budgeted
>>this way, the other way is to budget the PO to a project number. Since
>projects
>>span multiple offices the project is given its own budget. Project budgets
>>are broken down the same way except it goes project number, categories
period
>>then year.
>>
>>Thought I had it figured out with 5 tables (for the budget portion)
>>1. categories - holds category information
>>2. Period - holds period information
>>3. locations - holds location information
>>4. BudgetYear - holds year information
>>
>>These above four tables are in a one to many relationship to the budgets
>>table, the budget table contains the following fields
>>1. PeriodID - primary key from period
>>2. CategoryID -primary key from categories
>>3. YearID - primary key from budgetyear
>>4. LocationID primary key form locations table
>>5. BudgetAmount - The dollar amount for the budget
>>6. Budget ID - auto generated ID.
>>
>>When a PO is associate to a budget (according to the users selection of
>categories
>>year and period) the BudgetID form the budget table is placed into the
PO
>>table to link them
>>
>>Now.....what about my projects?
>>
>>Projects associate to budgets just like locations. A budget is either
>associated
>>with a location or project...I have thought of the following...
>>
>>1. Rename locationID to locationID\ProjectID....if the budget is for location
>>enter locationID and for project enter projectID...I don' like this Idea.
>>too much chance of having duplicate locationID and Project ID
>>
>>2. Add a column to the budget table called ProjectID and mark as 0 for
>all
>>budgets associated with Locations, I would also have to mark locationID
>as
>>0 for all project PO's
>>
>>3. Use subset tables, I read a little about these in a book. I guess
I
>>would have a LOCATIONBUDGET table and a PROJECTBUDGET table the would contain
>>the locationID and the projectID respectively and the BUDGETID form the
>budget
>>table. they would be linked in a one-to-one relationship to the budgets
>>table.....Have never seen this before and it looks as If I would have a
>ton
>>of joins in my queries.
>>
>>4. Create an identical table to Budgets and call it Projectbudgets....This
>>would work but I place the BudgetID (from either table) how do I know what
>>table it relates to also it would be more difficult to perform aggregate
>>functions. For example if I wanted to see the total $$ spent for period
>>on, including both project and regular.
>>
>>
>>Don't really know what direction to go..any direction would help. Some
tips...a
>>web link a recommendation for a book
>>
>>thanks
>>Bert
>

[mark]

Glad I could help.

I figured that was the case. You are not alone. Unfortunately it seems
companies try to do it all on their own and usually only hire "programmers"
to help get the coding done if necessary. It is tough to get them to see
the benefit of help up front. It is cheaper in the long run because modifications/new
programs/connecting systems eventually cost more than paying for help with
design.

If you have any other questions you can e-mail me - mknuttall @ yahoo . com
(take out the spaces).

Mark

"bert" <bertcord@corderman.com> wrote:
>
>thanks mark....
>that set me on the right direction...I would love to have someone come
in
>to help...to bad my compnay will not pay for it. Some how I got the complete
>job. DAtabse and the web app....hey its good experiance
>
>thanks again
>bert
>
>"mark" <mnuttall@nospam.com> wrote:
>>
>>Bert,
>>
>>Idea 1 sounds right - except for the name. I don't what would be best
>for
>>that. Something generic alike ParentID. And then add a field for parent
>>type. That way you can add more types in the future. And you wouldn't
>have
>>conflicts with keys. If you want to create a report that includes budgets
>>from both Projects and locations then use a Union like (psuedo code):
>>
>>"SELECT FROM BUDGET, PROJECT WHERE BUDGET TYPE IS PROJECT AND BUDGET's
PROJECT
>>ID EQUALS PROJECT's ID
>>UNION
>>SELECT FROM BUDGET, LOCATION WHERE BUDGET TYPE IS LOCATION AND BUDGET's
>LOCATION
>>ID EQUALS LOCATION's ID".
>>
>>This is a quick read. I would really need to do a more detailed analysis.
>> I would say hire a short term consultant to help with analysis/design
(and
>>give you some OJT). This would be better than getting a book (If they
are
>>good) and hoping you did it right.
>>
>>Mark
>>
>>"Bert" <bertcord@corderman.com> wrote:
>>>
>>>I am working on a database to track PO's and there associated budgets.
>
>>My
>>>company has 100 locations. Each of these locations budgets are broken
>down
>>> by categories (we have 8 ), period (1-12) and year. Some quick math....that
>>>accounts for 9600 separate budget items per year...Most items are budgeted
>>>this way, the other way is to budget the PO to a project number. Since
>>projects
>>>span multiple offices the project is given its own budget. Project budgets
>>>are broken down the same way except it goes project number, categories
>period
>>>then year.
>>>
>>>Thought I had it figured out with 5 tables (for the budget portion)
>>>1. categories - holds category information
>>>2. Period - holds period information
>>>3. locations - holds location information
>>>4. BudgetYear - holds year information
>>>
>>>These above four tables are in a one to many relationship to the budgets
>>>table, the budget table contains the following fields
>>>1. PeriodID - primary key from period
>>>2. CategoryID -primary key from categories
>>>3. YearID - primary key from budgetyear
>>>4. LocationID primary key form locations table
>>>5. BudgetAmount - The dollar amount for the budget
>>>6. Budget ID - auto generated ID.
>>>
>>>When a PO is associate to a budget (according to the users selection of
>>categories
>>>year and period) the BudgetID form the budget table is placed into the
>PO
>>>table to link them
>>>
>>>Now.....what about my projects?
>>>
>>>Projects associate to budgets just like locations. A budget is either
>>associated
>>>with a location or project...I have thought of the following...
>>>
>>>1. Rename locationID to locationID\ProjectID....if the budget is for location
>>>enter locationID and for project enter projectID...I don' like this Idea.
>>>too much chance of having duplicate locationID and Project ID
>>>
>>>2. Add a column to the budget table called ProjectID and mark as 0 for
>>all
>>>budgets associated with Locations, I would also have to mark locationID
>>as
>>>0 for all project PO's
>>>
>>>3. Use subset tables, I read a little about these in a book. I guess
>I
>>>would have a LOCATIONBUDGET table and a PROJECTBUDGET table the would
contain
>>>the locationID and the projectID respectively and the BUDGETID form the
>>budget
>>>table. they would be linked in a one-to-one relationship to the budgets
>>>table.....Have never seen this before and it looks as If I would have
a
>>ton
>>>of joins in my queries.
>>>
>>>4. Create an identical table to Budgets and call it Projectbudgets....This
>>>would work but I place the BudgetID (from either table) how do I know
what
>>>table it relates to also it would be more difficult to perform aggregate
>>>functions. For example if I wanted to see the total $$ spent for period
>>>on, including both project and regular.
>>>
>>>
>>>Don't really know what direction to go..any direction would help. Some
>tips...a
>>>web link a recommendation for a book
>>>
>>>thanks
>>>Bert
>>
>