I've hit a brick wall defining my entities, please help (1 Viewer)

[NBRJ]

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:

• 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).

Now this is where I get a little hazy with identifying entities and need some input into if it's right...

• 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 also has and this is the bit I'm confused about if it should be in this entity:

• 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.

tblPackage and tblBundle both have a Status field:

• Unassigned
• Assigned
• Work in progress
• Testing
• Approved

With this as it is now, I was hoping to pull off workload reports that show a list of all unique software item requested whether they have a package (tblRequest.DecisionPackage), what state (tblPackage.Status) and who is assigned (tbPackage.StaffPackager). If completed, show whether they have a Bundle (tblRequest.DecisionBundle) and what state (tblBundle.Status) and who is assigned.

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....

 

[MarkK]

• 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).

I don't see how your ERD handles this data structure. Your language is that a request has 1 or more locations. I don't see your request table as having this "many" side of requested installs adequately represented, except through the Install table, but at the time of the request, the install doesn't exist yet, so how does a request know what locations it has AT REQUEST TIME!

Take a look at how a typical order is structured, with an Order table where each row is a single Request, and the many OrderDetail records, each one defining a different product ordered. In your case the OrderDetail record will always have a quantity of 1 software title, and your OrderDetail should link to a location too, not just a product. So your request is then a fairly classic Order/OrderDetails arrangement, and each location can be approved or not individually, but this is a structure I don't see in your ERD.

IMO.

 

[NBRJ]

Well I did say that the tblInstalls table name keeps changing. I'll change it back to tblRequestLocationJunction (or tblRequestDetail). That make it clearer how tblSubject, tblRequest throught to tblLocation works?

The attached screenshot shows the rough function test of the relations on a form (completed unformatted and nothing done to it).

 

[MarkK]

OK, so if you renamed your Install table to RequestLocation that makes more sense to me, but then where do record the date the install is actually performed, and by who?

As far as tblRequest.Descision goes, what if one RequestLocation is approved and another is not? Is that possible? What if one subject needs two different software items? Is that two different requests?

I would expect a less information rich Request object, which is like an Order, and richer line items that might be RequestSoftwareLocation objects. Then one request can govern orders for multiple different software items at multiple different locations for one subject (customer).

So one request should contain one or more of these objects . . .

tRequestSoftwareLocation
RSL_ID
RequestID
LocationID
SoftwareID

. . . because those are in fact the three dimensions of the software installation request, 1) what request does it belong to (gives us the customer), 2) what is it a request for, 3) where will the installation occur.

And then there's the installation, which IS the decision. Like, the thing you actually will install, so each RSL row should have a related (child) install which would look like . . .

tInstall
InstallID (PK)
RSL_ID (FK) <- gives us the RequestID, LocationID and requested SoftwareID
SoftwareID (FK) <- and redefines what was installed, maybe the same as requested, maybe not. This IS the decision.
InstallDate
InstalledBy

. . . and installs can exist in the future, so this can begin to serve as a scheduling tool too.

IMO.

 

[NBRJ]

As far as tblRequest.Descision goes, what if one RequestLocation is approved and another is not? Is that possible? What if one subject needs two different software items? Is that two different requests?

I forsaw Request as being for 1 software item for 1 or more locations. So each request would be for 1 software, but I see where you're going.....

I would expect a less information rich Request object, which is like an Order, and richer line items that might be RequestSoftwareLocation objects. Then one request can govern orders for multiple different software items at multiple different locations for one subject (customer).

So one request should contain one or more of these objects . . .

tRequestSoftwareLocation
RSL_ID
RequestID
LocationID
SoftwareID

. . . because those are in fact the three dimensions of the software installation request, 1) what request does it belong to (gives us the customer), 2) what is it a request for, 3) where will the installation occur.

At one point I did have a ERD that had RequestDetail having links from tblVersion, tblLocation and tblRequest but scraped it as that would take a lot of data entry when the case is more often than not a Software Item is required in many locations and I didn't want to have to enter data for each item for each location. But that's probably my lack of knowledge on how to do bulk entries like that on a form....?

And then there's the installation, which IS the decision. Like, the thing you actually will install, so each RSL row should have a related (child) install which would look like . . .

tInstall
InstallID (PK)
RSL_ID (FK) <- gives us the RequestID, LocationID and requested SoftwareID
SoftwareID (FK) <- and redefines what was installed, maybe the same as requested, maybe not. This IS the decision.
InstallDate
InstalledBy

. . . and installs can exist in the future, so this can begin to serve as a scheduling tool too.

IMO.

This is MUCH better. MUCH better. The only thing I'm confused by is they request tblVersion (the software item), but they get tblBundle which is an action on tblVersion. Could I have SoftwareID, PackageID, BundleID in that tblInstall?

It's kinda like a manufacturing order I guess, you order a sheet of metal + you detail of how you want it cut/processed and get something that is the sheet of metal but different?

 

[NBRJ]

Or when you order food online, and they subsitute what you ordered for a similar item? Which is really annoying, but it kinda mirrors what may happen here?

 

[NBRJ]

Updated ERD attached. I'm still unclear how to link the bundle (the deliverable) to all this?

 

[MarkK]

The only thing I'm confused by is they request tblVersion (the software item), but they get tblBundle which is an action on tblVersion. Could I have SoftwareID, PackageID, BundleID in that tblInstall?

What is the process that converts what that 'customer' requested into what you actually install? I don't understand your 'product' well enough to advise on this. It looks like one bundle might point back to multiple packages and versions, so the one BundleID in tblInstall might mean that tblInstall simply can't have a single PackageID or SoftwareID (if the Bundle says otherwise). Could the Package be multiple softwares? And if so, you still have an ERD problem, because then, if the 'customer' requests two different softwares, you'll want to build that into the Package, right? Or not? See what I mean -> I don't get your Software/Package/Bundle model, or how and when it gets composed.

 

[NBRJ]

Software item = the installer.

Package = action taken by us on a single installer to make it work with the deployment technology and, optionally, any configuration requirements. A software item may become one or more packages, as one subject may want it configured one way, another may want it another. So for example, Adobe Illustrator 2015 for Fine Art might have different interface configuration from Adobe Illustrator for Graphics.

Bundle = 1 or more packages, and is considered the deliverable, for example, the main software item, plus plugins or addons. The trouble is, is that the customer isn’t always going to know the additions needed to make something work (if they’re plugins for example) but they may know if they’re addons or child software items but not always. So they may or may not request them. I did originally have an self join RequestDetailAssociations table but we discussed this in a meeting and thought that the ConfigInfo could be used to state such things and then we could properly represent it through the packaging/bundle info. Packaging and Bundling info isn’t something the customer really has any need to know about, but from the build side, we need to keep track of it.

It’s such a tricky thing to represent and causing me much grief.

 

[NBRJ]

I should also say in both Package and Bundle there is a Status field, which select the build state it's at. This is to try to form work lists; what's to do, what's complete, etc.

Status would be:
1. Unassigned (not yet built)
2. Assigned (not yet built, but assigned to a staff member to do the work)
3. Work in progress (action being taken)
4. Testing
5. Complete (ready for bundle if package, ready for deployment if Bundle).

I was hoping this would give us the ability to run work required type reports on requested software items...

 

[MarkK]

At one end you need to provide your 'subject' (customer) with a valid list of software they can request. Then your request object, with it's many details (softare + location), can exist and be completely defined, independent of everything else. That system must be able to stand on its own.

Then you have to create a product that fulfills that request, which, it sounds like, is the "bundle," which is the deliverable. So for one RequestSoftwareLocation, you can schedule the install without knowing the BundleID. Or you can add the BundleID without knowing the install date.

Here's an awful edit of your ERD that might explain it better . . .

 

[NBRJ]

I was close, but no cigar!

There will be a list of software (tblVersion), but the software will be both software for which we have a deliverable (state: live) and software we have yet to make a deliverable (state: proposed) - and may or may not do so. They can propose software we don't have yet and we may or may not respond. Unfortunately it's not so black and white that we state what they can have, but nor can they get everything they ask for without some restriction.

Once we get all the requests we will do some rationalisation to ensure standardisation (i.e. version control) and prevent spiraling licensing costs. That's why I was keen to have a decision which is what FIDSoftwareVersion in tblInstall does. It may be that Adobe Illustrator 2015 is the current "live" one, but if 90% are asking for Adobe Illustrator 2016 ("proposed"), we will respond so that will be the future live one and 2015 becomes "archived". If only 10% are asking for the newer version, we will stick - go with the majority of what the business needs (unless something is known to be unstable, or it is cost prohibitive because the request is aiming rather high (that happens a bit!) etc).

Once that's decision is made and the DB updated (FIDSoftwareVersion in tblInstall) we will decide if there is a bundle that fits or whether we need to create one. If we need to create one, then the volume of requests for software items will give us priorities. If only 1 subject requests an item for 1 room, but another software is requested for >1 subject and lots of rooms, then we should be able to prioritise workload (the full tblLocation has workstation numbers attached to locations to further facilitate this inspection for priorities).

Then the Status field in tblPackage and tblBundle should enable us to track the status of that build towards a deliverable. I'm thinking this works now. It feels like it works.

I think the only thing I may need to do is a self-join to RequestDetails so users can, if they want, associate requests to each other. I'm in two minds to do that, or just ask them to request the major software item, and detail dependancies in Config Info.

MarkK, thank you for all your time and input, it has been extremely helpful in focussing and providing clarity. I have felt rather lost at sea over this part of it. It's turned into a bit of a beast to conceptually get it sorted.

 

[MarkK]

You're welcome. Congrats to you for good questions and stick-with-it-ness. Cheers,