Are the relationships between my tables *totally* wrong? (1 Viewer)

Hello

It's been a good number of years since I've been involved in databases so take me as a newbie. I'm hoping someone will have the Eureka moment that has failed me so far. I'm sorry that this is the 2nd post to the forum, I'm honestly trying not the be rude by jumping in with a HELPME-type post, but I really need another brain to look at the problem I'm having.

I have a relationship design question regarding a problem that has driven me nuts for the last week. The purpose of the database I'm making it this: I work for an educational establishment with about 100 rooms ("locations") with computers installed, and they all have the potential to have different software requirements. We're trying to track each indivdual software request for each location from the current academic year (1516) to the next (1617). The result of which will eventaully give us a work schedule (to package the software and then another to deploy it), but I haven't got to that point yet, I'm still designing how we're going to get the request data in and handled in logically-related tables rather than one massive messy table. We've tracked this process previously with a number of spreadsheets with no data validation so we're now doing this small DB in hopes the headache will be the packaging work itself and not the keeping track of it all as well.

A request for a piece of software comes in with the following information (we can't change how we're doing this at the moment, this is how the business wants to handle it):

• Location
• Software Title
• 1516 Software Version
• Change Required
• 1617 Software Version
• SME

So an example of a request would be (a very simple one):

Location: Main Building, Room 3.02a
Software Title: Microsoft Office Word
1516 Version: 2013
Change required: Update (choice of No change, Update, New SW, Remove)
1617 Version: 2016
SME (Subject Matter Expert): Bob Smith

The pertinent tables I've got so far are:

• tblBuilding (id, abbreviation, fullname)
• tblLocation (id, fidbuilding, roomno, description, #PC, #Mac, etc)
• tblRequest (id, fidlocation, fidsoftware, 1516version, changetype, 1617version, fidsme)
• tblSoftware (id, developer, suite (optional), title)
• tblSoftwareVersion (id, fidsoftware, version, platform (eg: win, win64, win32, mac, linux), licensetype, etc)

NOTE: tblSoftware and tblSoftwareVersion is split into the parent-child relationship because of the staggering # of versions we could have. For example, sw title MS Office Word would have relatively few # of versions: 2013, 2016, 2011 (for Mac), 2016 (for Mac). And we have 100s of SW titles, before we add in multuple versions for each title. I know Developer should have it's own table, but for now we're going without.

And tables that I'm not sure should exist or not:

• tblRequestVersion1516
• tblRequestVersion1617

The relationships of those tables are:

• tblBuilding 1:M tblLocation
• tblLocation 1:M tblRequest
• tblRequest M:1 tblSoftware
• tblSoftware 1:M tblSoftwareVersion

And subject to change because my mind just can't figure it out:

• tblRequest 1:1 tblRequestVersion1516 1:1 tblSoftwareVersion
• tblRequest 1:1 tblRequestVersion1617 1:1 tblSoftwareVersion

NOTE: tblSoftwareVersion lists will be filtered dependant on the record selected from tblSoftware.Title. Hopefully. Well, they have to be, but this needs solving first.

My original design had tblRequest M:M SoftwareVersion which was selecting two entries from tblSoftwareVersion in one record of tblRequest which goes against all I remember about database table relationships. However just one JUNCTION table can't solve that M:M because I need two entries per field from tblSoftwareVersion in tblRequest (pretty sure this is now gibberish).

I hope this all makes sense. It feels like I've written War and Peace. I hope someone is still reading at this point and, better still, inclinded to respond and help me out of my misery! I'm going to try to upload a jpg of the relationship diagram, because a picture paints a thousand words (probably literally, in this case).

I've googled a LOT, but can't find an example that mirrors what I'm trying to do. Which probably means the design is ALL wrong.

Please help me (Sorry!)

Jo

No those tables (and fields) should not exist. You use your database to store data, not your table/field names. 1516/1617 (and the years to come) should be values input your tables, not the names of tables or fields.

What I believe that means is that your Request table should loses those 2 fields, gets a field called [Version] and a field called [VersionYear]. Then instead of 1 record for a Request, you would have 2 records (1516 would go into 1 record and 1617 would go into another record). It would also allow you to get rid of those RequestVersion tables.

Also, your Software table is incorrectly placed. Request should be directly linked to SoftwareVersion (via IDSoftwareVersion and the [Version] field I described above). Then, off to the right of SoftwareVersion should be the Software table, linked via IDSoftware and FIDSoftware.

Lastly:

We're trying to track each indivdual software request for each location from the current academic year (1516) to the next (1617)

Click to expand...

That doesn't sound accurate based on the other parts of your post. The above statement makes it seem they are turning in 2 requests--one to upgrade this year then another to upgrade next year. That's odd, why not just upgrade to whatever is wanted for next year now?

I bet you are using this to track what software is on which system. The 1516 data isn't really a request for any action, but it tells you what is currently on the system and 1617 is really the only version you will be upgrading to.

My advice in that instance is to not do it that this way. If you want a software version history, you should build tables to accomodate that. This method seems like you are trying to kill two birds with a stone designed for killing one.

Oh thank you Plog! I'm sorry I took so long to respond, I was considering the useful info in your response and then pondering overnight the revised plan.

The 1516 data isn't really a request for any action, but it tells you what is currently on the system and 1617 is really the only version you will be upgrading to.

Click to expand...

This was the moment of clarity. I was just thinking about it from this year with no data in and getting that data in plus next year's requests. Which is a one time event. Every year from now on, we should just get what they want for the next year.

I'm attaching my revised plan, what do you think?

I think it looks fine based on what I know. I don't fully understand what some of your tables/fields are for (SME, Faculty), but it looks to serve your ultimate purpose better than the initial attempt. Here's a few notes:

1. Building & Faculty have the same structure. Usually you put both sets of that data in the same table, then use another field to designate if a record is one or the other (Faculty or Building).

2. PointOfContact & SME have the same structure as well. I would attempt to get their data into one table as well.

3. Location.TotalComps probably shouldn't be a field in a table if its calculated. Instead you just build a query to calculate it.

4. Installs.InstallationYear might better serve you if it held a specific date and not just a year. If you have to reinstall software you can add a record in Installs to let you know that, with a date you could discern that information, with just a year it will look like a duplicate record.

I was looking at the SME and PoC tables yesterday and wondering about merging them - which I've now done and logically works great.

As for Faculty and Building, unfortuantely I can't merge those. Buildings aren't uniquely owned by Faculties. A building for example, can have locations (rooms) owned by more than one faculty so they will have to remain seperate.

Location.TotalComps is a calcuated field, I'd read those are allowable. I'll refer to it when running queries in terms of figuring out workload priorities (i.e software needed by 1000+ machines will be a higher priority than software used by 100). Obviously if we were getting into detail, we'd be tracking number of classes (and enrolled students) in rooms and may be even modules to software relationship. I.e. sw may be needed for 1000 machines but only used at the end of term, whereas the software used by 100 machines could be needed every day from day 1. Luckily for me they don't need that detail. That sounds like a nightmare database to build. And the business doesn't need that for the point of this database. Design to requirements, right?

InstallationYear - I see your point, but we don't need to expand it to include installation date if we're not going to use that data.

Thanks for your input so far - it'd have taken me ages to rework it to that point!

in general, this is the problem

A request for a piece of software comes in with the following information (we can't change how we're doing this at the moment, this is how the business wants to handle it):

Click to expand...

It shouldn't be the business owners decision to determine how the IT department fulfils a request. It really shouldn't. OK they want to monitor current software and requests for software changes. It shouldn't be their job to mandate how the IT department undertakes to manage and process that information.

Their idea of how the storage should be implemented may be nonsensical or even not possible.

I would just provide a solution that gives them the inputs and outputs they want within a relational system, and if necessary demonstrate how you have accomplished that.


as a footnote. Also in general a 1:1 table link is an error

tblRequest 1:1 tblRequestVersion1516 1:1 tblSoftwareVersion
tblRequest 1:1 tblRequestVersion1617 1:1 tblSoftwareVersion

Click to expand...

In this case 1:1 means both tables have the same matching PK. This is not the same as 1:m where you (probably) only have at most one item on the many side, in which case the PK of the 1 table is an FK in the many table.

A 1:1 table is always unnecessary, and the data could be dealt with a single table only. the linked table is added for some special reason other than formal data design. (eg, there are too many fields for a single table, or data privacy is required for certain fields)

Hi Dave,

Plog was awesome and put me on track with my design - solved the 1:1 kerfuffle. Thanks for the clarification on why not to do it.

As for your comment about how the business wants us to handle it, I agree fully. I'm going to present this plan soon, once I figure out the one major stumbling block I have left (which I'm going to ask about in the Queries forum, and I'll link to that thread from here in case anyone is interested in following), and then hopefully they'll see what I've done is much more flexible with less room for error from the data... which has caused us problems in the past from the spreadsheets.