Hello
I have a problem finalising my ERD for my database. The current diagram allows for the main volume of data-entry that is needed, but I’m struggling with the entities that will manipulate that data. I've started the forms & queries, but I'm stuck when trying to plan the forms/queries/reports for manipluation of data and I'm struggling to do it with my current ERD (attached). But it may be down to me not knowing how to do the forms / queries for what I want to do, rather than the ERD is wrong. Therefore I need some input. The project managers seem happy with the ERD but I'm not convinced it logially works.
What the database will do:
It is for an educational establishment, that has a project every year to (wipe and) reinstall software on student workstations in various locations in response to requirements for the coming academic year from the various subject areas. The requirements are analyised and a decision is made on whether requests are rejected, subsituted or accepted. Once a decision is made the software items are packaged for the desired deployment method, tested, then installed.
What the current ERD shows so far:
One thing that is throwing me off with this ERD is when more than one subject areas request the same thing it results in 1 item of packaging/bundling. How can I bulk update those records?
I've attached two ERDs. I'm not sure whether all these relationships are needed in the second one?
I'm sorry if this is garbled. I'm very ill right now (student plague!) and trying to work on this is tricky! I hope it makes sense....
I have a problem finalising my ERD for my database. The current diagram allows for the main volume of data-entry that is needed, but I’m struggling with the entities that will manipulate that data. I've started the forms & queries, but I'm stuck when trying to plan the forms/queries/reports for manipluation of data and I'm struggling to do it with my current ERD (attached). But it may be down to me not knowing how to do the forms / queries for what I want to do, rather than the ERD is wrong. Therefore I need some input. The project managers seem happy with the ERD but I'm not convinced it logially works.
What the database will do:
It is for an educational establishment, that has a project every year to (wipe and) reinstall software on student workstations in various locations in response to requirements for the coming academic year from the various subject areas. The requirements are analyised and a decision is made on whether requests are rejected, subsituted or accepted. Once a decision is made the software items are packaged for the desired deployment method, tested, then installed.
What the current ERD shows so far:
- The buildings (tblBuilding) and what locations are in them (tblLocation), and to which subject each location belongs (tblSubject (from tblFaculty and tblSchool)).
- The software items (tblVersion (from tblDeveloper and tblTitle)). These are the basic installers, and not ready for deployment. A software item has a State field: proposed, live, archived, obsolete.
- When a software item is processed it becomes 1 or more packages (tblPackage). A package is based on the software item and configuration requirements and given a identifier that distingishes it. For example Adobe Illustrator 2015 may have two versions, because one will have it's interface configured differently for than the other.
- A package is deployed via a "Bundle" (tblBundle). A bundle will have 1 or more packages (tblBundleComponents is the junction table), to accompodate when a piece of software needs associated software items to run (i.e. plugin's etc).
- tblRequest: A request (tblRequest) is made by a subject (tblSubject) for an item of software (tblVersion) for 1 or more locations (tblLocation) for an academic year (tblRequest.AcademicYear).
- tblSME is the subject matter expert and is 1 or more members of staff who will ultimately be called on to test the resulting deployment bundle for that request and that it works how it's supposed to for that request.
- There is a self-join tble (tblRequestDependents) when a requested items is realated to another software item requested.
- The tbl between tblRequest and tblLocation keeps changing the name. It is currently tblInstall because it shows ONE software item for ONE location - which is how we'd track install status.
- tblRequest.Decision (Accepted, Rejected, Substituted)
- If rejected, no work done.
- If subsituted, the software item decided on will go in = tblRequest.DecisionSoftwareItem
- If accepted the software item (tblRequest.FIDSoftwareItem) = tblRequest.DecisionSoftwareItem
- tblRequest.Package: the package created (or chosen, if one already in the DB will fit)
- tblRequest.Bundle: the bundle created (or chosen) for deployment.
- Unassigned
- Assigned
- Work in progress
- Testing
- Approved
One thing that is throwing me off with this ERD is when more than one subject areas request the same thing it results in 1 item of packaging/bundling. How can I bulk update those records?
I've attached two ERDs. I'm not sure whether all these relationships are needed in the second one?
I'm sorry if this is garbled. I'm very ill right now (student plague!) and trying to work on this is tricky! I hope it makes sense....