Is this type of functionality done at form level, not table? (1 Viewer)

NBRJ · Feb 13, 2016

Can someone point me in the right direction.

I have the following relationships between 3 tables:

tblSWVersion 1:M tblInstalls
tblSWVersion 1:M tblPackage 1:M tblInstalls

The table's fields are as follows, with example data:

tblSWVersion

Code:

[B]IDSWVersion[/B]
SWV1
SWV2
SWV3

tblPackage

Code:

[B]IDPackage    FIDSWVersion[/B]
P1           SWV1
P2           SWV1
P3           SWV2

tblInstalls

Code:

[B]IDInstall    FIDSWVersion        FIDPackage[/B]
I1           SWV1                P1 or P2 as a selection choice
I2           SWV2                P3 only
I3           SWV3                Nothing in list - no associated package yet
I4           Not yet selected    Nothing in list

If I wanted to have a filtered list from tblPackage in tblInstalls.FIDPackage based on the selection in tblInstalls.FIDSWVersion (from tblSWVersion.IDSWVersion), is that possible at table level?

Or is this something I can only do at form level? I'd read a few threads about similar (but not quite) to this and they can only be solved at form level via Cascading Combo Boxes.

jdraw · Feb 13, 2016

Relationships are derived from Business Rules. Please tell us what these tables represent and how they (SWVersions/Packages/Installs) fit together in business terms.

NBRJ · Feb 13, 2016

I work for an educational establishment and the database, of which listed above, is part of it. It tracks software requested for each room (location). Perhaps if I'd included tblocations it would've been clearer.

tblLocations 1:M tblInstalls M:1 tblSWVersion
(tblInstalls resolves the software to locations M:M relationship)
A record in Installs is a request for one individual SW installation in one location for a particular year.

Package is the package deployed of that software. Now originally the Package info was in SWVersion, but I've realised today that can't be, as we could have two packages for the same version of software. A very simple example would be:

Adobe CC Illustrator 2015.1 (a record in SWVersion) could have two packages associated with it, because the default interface can be configured to suit two vastly different locations. The software isn't different, just the interface looks different to the user and saves them 10 minutes of setting up to how they need it every lesson, hence a different package. So the package entries could be:

Adobe CC Illustrator 2015.1 - Graphics Design Config
Adobe CC Illustrator 2015.1 - Fine Art Config

Sooo, tlbPackage is new today. I could shove the fields back into SWVersion, but I thought it was good practice to avoid duplicate data in a table? I may be destroyed what was a lovely working design by adding in tblPackage :banghead: (Plog helped me out with the pre-tblPackage dilemma I had the other day)

Also, importantly! The package may not have been created at the time the request comes in (in tblInstalls), but there will be an entry in SWVersion for the software item. Packing is a post-request (Installs) task. So that field will be updated after the rest of the record is created.

NBRJ · Feb 13, 2016

NBRJ said:
Also, importantly! The package may not have been created at the time the request comes in (in tblInstalls), but there will be an entry in SWVersion for the software item. Packing is a post-request (Installs) task. So that field will be updated after the rest of the record is created.

Which is why there is still a link direct from tblInstalls to tblSWVersion.

MarkK · Feb 13, 2016

You repeatedly refer to tblInstall as being a request. Why not call it tblRequest? Does a request always result in an install? Is the item installed always the same as the item requested? Is an item ever install only after repeated requests? Should Request and Install be different tables?

NBRJ · Feb 13, 2016

Yep, a request becomes an install - it'd be a 1:1 or 1:None between REQUEST and INSTALL if I had to separate them out. Therefore rather than having a 1:1 which I understand is not good practice, we'll just delete a entry if it doesn't become a actual install. Does that sound wrong?

MarkK · Feb 13, 2016

Well, wrong is a strong word. Obviously the system has to satisfy you, and if it does that it is fine, but I wouldn't delete anything. I would want to be able to report on how many requests I got, and deleting requests that aren't fulfilled kills your stats.

And I would likely put the Installs in separate table from Requests. In the real world they are completely distinct in all respects. One might exist and the other might not, and if there was no install, there should be no install record. In a 1:1 table, the install exists, but it has no data. Maybe that is the purist in me, but that is the route I would take.

Also, can there be a Request for many things? Then a Request might have many Installs?

Hope this helps,

NBRJ · Feb 14, 2016

I guess if there is an entry in FIDPackage in tblInstalls for a record, that indicates the request was successful and a install made. I've just spent an hour or so trying to rework the ERD and just can't make it work, because I understand logically where you are coming from.

If (old) tblInstalls becomes tblRequestInstall and then there is a (new) tblInstalls (confusing, yes) that is completed on agreement of work to be done

tblLocation 1:M tblRequestInstall M:1 tblSWVersion (because a request still needs to lookup a SW title)
tblLocation 1:M tblRequestInstall 1:1 tblInstall M:1 tblPackage M:1 SWVersion.

But the information 1:1 between Request and Install is already indicated by the current layout of the tables by FIDPackage in (the current) tblInstalls.

I think. As it is, it's currently working for me, except for that filter for FIDPackage in (current) tblInstalls based on the FIDSWVersion selected for that record so back to the original question, really. Anybody got any advice for that?

Thanks for making me double check my design in detail - all learning and questioning is good.

Although, I'm on a deadline, so at the moment adds to the panic

jdraw · Feb 14, 2016

Just wanted to add that I agree with Markk. Let me give an example. Consider a business where you Apply to a Program to get some funding to carry out some work. Many Applications could be received, but when analyzed do not meet the Program requirements. Those meeting the requirements and deemed worthwhile become Projects and get funding. Also, the Projects are monitored against milestones and if they meet the conditions, they get funds for next phase and continue. Sometimes Projects don't meet milestone conditions and are Cancelled. There are also instances where an Application, or a partial Project are Withdrawn for a number of reasons.

From an analysis of Programs, takeUp, success etc, it is often required to account for Applications, Rejections, Withdrawals, Projects, Benefits........

All this to say Applications which do not proceed to Projects can not just be ignored.
My thinking is that my Application is similar to your Request Install. However, you know your business better than the readers. So if this does not apply to your situation, ignore it --just ignore it from the perspective of having considered the situation.

Good luck

MarkK · Feb 14, 2016

I would expect more like . . .

tblRequest
RequestID (PK)
LocationID (FK)
PackageID (FK) <- can a request have more than one thing in it?
RequestDate

tblInstall
InstallID (PK)
RequestID (FK)
PackageID (FK) <- or is this in the request?
InstallDate

tSoftware
SoftwareID (PK)
Name

tVersion
VersionID (PK)
SoftwareID (FK)

tPackage
PackageID (PK)
VersionID (FK)

jdraw · Feb 14, 2016

nbrj,

Perhaps you could post a jpg of your ERD.

NBRJ · Feb 14, 2016

Ok, I've attached my (reduced for clarity) ERD (see ERD-tblPackage-REDUCED.PNG)

I've also attached a diagram of what was suggested by MarkK (ERD-suggested-1to1.PNG) but with the addition of the link between Request and SW version. I must have a direct link from Request to SWVersion. A package may not have been created yet, so need a direct link to SWVersion to say what they want.

can a request have more than one thing in it?

A request(install) is for 1 swversion in one room for 1 year. A location will have many requests.

RequestDate
InstallDate

I don't need to include request or install dates - these are irrelevent beyond the academic year the sw is needed for.

Even if I go down and split Request and Install I still need to filter the packages limited to the SWVersion requested which brings me back to the question in my original post. Please can someone help with that query (as per original the post or with Request/Installs)?

NBRJ · Feb 14, 2016

Also, I'm still a little uncertain of the 1:1 between Request / Installl. I thought they were the devil's work and must be avoided

jdraw · Feb 14, 2016

Sounds to me that your Package is really an Interface or Configuration (since your sample showed Config0.

Do you have 2 or 3 line descriptions of each of your Entities? It's surprising how helpful that can be. Good for documentation, communications and support especially if they are written in plain, simple English. Often a good idea to bounce such definitions off colleagues who will interface with the database application.

MarkK · Feb 15, 2016

No, I don't recommend 1:1 either if that was unclear in my post. The "route I would take" is to separate the Install table from the Request table, and I would relate them One Request : Many Installs.

NBRJ · Feb 15, 2016

jdraw said:
Sounds to me that your Package is really an Interface or Configuration (since your sample showed Config).

Do you have 2 or 3 line descriptions of each of your Entities? It's surprising how helpful that can be. Good for documentation, communications and support especially if they are written in plain, simple English. Often a good idea to bounce such definitions off colleagues who will interface with the database application.

Developer: the software developer of titles (i.e Microsoft, Adobe)
SWTitle: Title of the software (i.e Word), excludes version or platform info. Includes optional suite information (i.e, Office)
SWVersion: A specific version of a software title (2016, Windows / 2016 Mac). Details version, including platform.
Package: a single item of software (derived from Developer, SWTitle, SWVersion) that is packaged for use by a particular deployment method and includes identifying configuration options.
Install (as it is currently, no split of Request/Install): A item of software requested for a location for an academic year and it's resulting deployment action.

So for "package", for a windows-specific software deployment can be via MDT (results in software installed locally on the machine) or AppJ (virtualised delivery of applications, not locally deployed). The package technology for MDT and AppJ is different. You cannot deploy a AppJ package to the machine via MDT's Lite Touch, nor can you run a MDT installation package on AppJ.

NBRJ · Feb 15, 2016

Can anyone respond the original query? Is this possible to do? Or only at form level? Thanks

NBRJ · Feb 15, 2016

MarkK said:
No, I don't recommend 1:1 either if that was unclear in my post. The "route I would take" is to separate the Install table from the Request table, and I would relate them One Request : Many Installs.

but a Request is for 1 SW item in 1 Location, a request can't have many installs as I'm not tracking by workstation within a location. It's still 1-to-1. Installs is just the Request but with an association to package, which still leaves me with the original problem.

MarkK · Feb 15, 2016

Is this the original problem?

NBRJ said:
If I wanted to have a filtered list from tblPackage in tblInstalls.FIDPackage based on the selection in tblInstalls.FIDSWVersion (from tblSWVersion.IDSWVersion), is that possible at table level?

I would do this using SQL, so in a query, or by modifying SQL in VBA code. The query, or VBA modified SQL, can then be used to drive whatever user interface element you want, subform, listbox, combobox, or a recordset from which you take a completely custom approach to representing the data to your user.
Hope this helps,

NBRJ · Feb 15, 2016

Hi MarkK,

I've been revisiting your separation of Request and Installs idea, but I'm trying to define the relationship and cardinality. Originally I had this below and couldn't wrap my head round it:

A request (for a software item in a location) will result on none or one Install (in that location).
A Install (in a location), of a Package, is in response to a one request for a software item.

I've had two meetings today about the DB today and they thought request/installs doesn't need to be split, but information from those meetings now have me thinking down your lines.

They want queries to compare all the like software-titles requested and, if different versions are requested, a definitive version will be chosen (but not always, different locations may legitimately require the version requested). I was going to handle these type of issues through an UPDATE query and maybe have a SoftwareItemRequested and SoftwareItemConfirmed (within Request) - the latter being updated by a UPDATE query. These actions will be done carefully!

Also, I've found out that the version requested and then installed may be removed and a updated (different) version installed during the year (and not via a request, but just because the IT department decide to do so). I was thinking that Installs may be the way to handle that. So a Request MAY result in more than one install of a software item (but different versions) and may need a withdrawn/install state within the Install tbl.... In that situation i can see Request to Install being 1:M. But Install SWV may be different from Request SWV.

I'm beginning to hate this ERD. I just need to wrap my brain around this part of it.

Is this type of functionality done at form level, not table? (1 Viewer)

Registered User.

Super Moderator

Registered User.

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

Super Moderator

bit cruncher

Super Moderator

Registered User.

Attachments

Registered User.

Super Moderator

bit cruncher

Registered User.

Registered User.

Registered User.

bit cruncher

Registered User.

Similar threads

Users who are viewing this thread