a little design help thanks


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: a little design help thanks

  1. #1
    Bert Guest

    a little design help thanks


    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

  2. #2
    mark Guest

    Re: a little design help thanks


    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



  3. #3
    bert Guest

    Re: a little design help thanks


    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

    >



  4. #4
    mark Guest

    Re: a little design help thanks


    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

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center