CODEWITHSUNDEEP
ms-accessdatabase-design
user1870816
user1870816

Reputation: 3

Database design for projects

I have an old database with 2 tables:

table1=standard items

table2=all items

This database was first design to be a tool for a single project.

I like to have one single database that handles all projects, but that require a new design - what is the best design when need the database to do this:

  1. Make copies of standard items
    a. about 50 standard items and project mananger (PM) often need to make 2-3 copies of the same item.
  2. Make changes to new items from step 1
  3. Make query only on the new items from step 1
  4. Several project managers can work on step 1-3
  5. Changes on new items (step1) can occur during the project
  6. When the project is finish new items needs to be stored in the table with all items

Need help on this: a. what is the best practice, when i need to copy items (sometime several of the same item) b. how do i seperate new items between PMs c. how do i get all items together in table2

I really need help on best practice on this one.

Upvotes: 0

Views: 132

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

There is no need for two tables, you can indicate with flags and dates where an item is in terms of work. Consider the following sketch.

Project

ProjectID
Contact -- fk to PM id. This is just the lead, all other contacts are
           in the PersonsProject tables
Etc

Items -- All

ID -- pk
ProjectID - fk to Project
AssignedTo - fk, but it all depend on whether a person is in charge of an item
Stage and / or status -- fk to list
Description
Notes
Created date
Completed date
Deleted date
Deleted reason
Created by
Deleted by

Persons

ID
Etc

PersonsItems or PersonsProject

PersonID
ProjectID
Notes
etc

Possibly a table of assigned items

PersonID
ProjectID
ItemID

In addition, if you have a limited list of possible items, you might need an Item list table, in which case the ItemListID would go into the Items table and Description would come out.

Upvotes: 1

Related Questions