0

I'm trying to think of the best way to design a database, and I'm really running around in circles in my head unable to come up with the answer !

The aim of the database is to show what software is installed on computers. I'd like to be able to:

  • Group computers by locations
  • Search for software and display locations with that software
  • Have various versions of the same software (where I can also search for a specific version of software)
  • Have an overview of a location (count how many machines are in a location)
  • Have an overview of a location (display all the different software available, and how many computers have each software package installed - this is causing me big headaches!)

The tables I have are:

  • computers
  • locations
  • software
  • software_computer_relation_table

The tables break down as:

  • computer table has the computer name, and location ID in it
  • location table has the location ID, and a break down of the location (building, floor number and room number)
  • software table has the software id, software name, and version number (if the software name is the same, but version number different a new row is created)
  • software_computer_relation_table this has the ID of the computer name, and the ID of the software (couldn't use software name as a primary key because multiple rows are created with different versions).

The main problem I'm getting is when I try to give an overview of the location with what software is available (ignoring the version), and how many computers have that software installed within that location, because the software table softwarename column is not unique (because there can be different versions), a location can appear to have more software installs that machines actually available (because the software name can appear twice on one machine because the version number isn't included).

I think the solution to this, is to create a computer table, software name table, and a software version table (which links back to the software name table) however I cannot think of how I can link all three tables together in the way I want.

Please help me !

Any help / guidance / best practices given is much appreciated ! I'm new to SQL :)

Thanks, Josh

Josh Budd
  • 69
  • 2
  • 5
  • See [this](http://stackoverflow.com/a/24422479) and [this](http://stackoverflow.com/a/24071462/3404097http://stackoverflow.com/a/24071462/3404097) about base table choice. See [this](http://stackoverflow.com/a/23842061) and [this](http://stackoverflow.com/a/24425914) about query composition. (Or search & browse my answers with "statement".) – philipxy Jul 06 '14 at 02:50

1 Answers1

0

Your schema should work. With 1 change - the 4th table should have computer id, not name. You will JOIN (I'm going to use numbers 1-4 for your tables) 1 and 2 on location id; 2 and 4 on software id. 3 and 4 on software id (not sure you're worried about the name - the id as you designed it is the correct way to track it since vendors do change names). And obviously 1, 2, and 4 using these first two joins.

user3741598
  • 297
  • 1
  • 12
  • PS - please post your queries if you don't think they're working. – user3741598 Jul 02 '14 at 16:00
  • Thanks very much for the quick reply ! Slightly confused by this though, you've said to join 2 and 4 on software id, however if I only have the location ID (for example, room number), how can I specify a software id when I want to see all software ID's within the room? The way I've been doing it before was by getting all computers by location ID, then JOINing that result with the computer / software relation table, then JOINing that with software name to get the software within the room, and added software name together to get total number of installs. However because I don't specify... – Josh Budd Jul 02 '14 at 16:46
  • the version, 1 computer could be reporting with multiple installs of software (different version but I can't work out how to separate it), then because I'm adding the instances of software name in a location, the end result can be a room looking to have more computers then it actually does. Hope this makes sense. The software table looks like this: Softwarename: Softwareversion: GIMP 2.3.1 GIMP 4.2.1 GIMP 5.2.3 Although the computer only has GIMP installed, the SQL thinks it has 3 because I'm not including the version. – Josh Budd Jul 02 '14 at 16:52