Struggling with database design

Question

I am creating a database which is an asset register. Each computer has a build number which belongs to a department and certain software which needs to be linked to it and what the priority is for it.

For example:

Build number: 12345
Location: Office

Adobe - Dreamweaver (priority high)
Adobe - Acrobat Reader (Priority Low)
Adobe - Shockwave (Priority Medium)

There are around 30 separate buildings and around 30 departments.

I can't think of an ideal way to do this? Should each building have a separate table? Help!

Answer 1

Well, we would need a couple different entities/tables:

**Department**
BuildNumber/Id - Primary Key
Name    

**Computer**
Id - Primary Key
BuildNumber - FK to department.buildnumber

**Software**
Id - Primary Key
Name

**ComputerSoftware**
Id - Primary Key
ComputerId - FK to computer.id
SoftwareId - FK to software.id
Priority

In this way, you could have a computer with id = 1 which has buildnumber 12345. Buildnumber 12345 belongs to only a single department, "office".

In the software table you would have Dreamweaver, Reader and Shockwave with id's 1, 2 and 3 respectively.

The table "ComputerSoftware" defines which software is on a specific computer. Say we have Dreamweaver and Shockware on computer 1, both with priority "high", there would be 2 rows in the ComputerSoftware table representing this. The data for this would look like this:

Id = 1
ComputerId = 1
SoftwareId = 1
Priority = "high"

and

Id = 2
ComputerId = 1
SoftwareId = 3
Priority = "high"

Hope this helps, John Wu's answer is great, but I wanted to give a really simple example too.

Answer 2

One approach to table design works this way:

1. Define your entities (what concepts are you working with)?

2. Define the KBM (Key Based Model) -- what are the relationships between them? What primary and foreign keys do you need in order to link the tables together?

3. Define you FAM (Fully Attributed Model) -- add attributes (columns) to the tables that describe each entity but do not participate in a relationship.

Sounds like your entities are builds, departments, and assets (or software). Start there. Figure out what keys you need to add to link them together, then add the remaining attributes (e.g. priority).