• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1698
  • Last Modified:

Database normalization

This will probably be an ongoing discussion any users with applied input will receive points

I'm creating a small access database to hold the information about every computer system at work. (about 40 +5 Servers). Hardware setup, users, location...

TONS of reasons. Main two are: so i know which systems need upgrades and when they were upgraded. SECOND  If I drop dead tomorrow someone can come in and see which users use each computer and vice versa.

So Far:
Ive created a visio diagram to layout DB


Open in new window

I'm not happy with what Ive set up. Any input to help normalize or improve on design would be appreciated. Main part I don't like is location table. I don't like having two pk but it seems impossible to get around.

Database Info:
Computer(Name) may have more then one user
LocID may have then one user.
UserName may use more then one computer

Again this is a discussion to improve DB design before I start creating Test DB

4 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
also, 1 computer can have more than 1 harddisk ...
and have more than 1 software (though I presume you wanted only to tell about the OS + office ...)

also, you might want to show the history of what computer was assigned to which user, for example.

so, let's clarify what you will need: a many-to-many relation table between the Computer table and each of the other tables, for example:

I will keep the ComputerName as PK for the table Computer, though you should have a virtual primary key instead ...

ID  (PK)
ComputerName (FK => Computer.Computername)
UserName (FK => User.UserName)
Start_Date datetime
End_Date (optional) datetime

Open in new window

does this sound better to you also?

The PK should never carry meaning, i.e., it should be a sequential number, a GUID or some other form of reference.

Never use a job number or a user name for the primary key. Why? Because if the user gets married, every reference in every table will need to be changed, not a simple task.
Further, use the same name for the PK in all of your table: I suggest "id".

FK names take the format of "tablename" + "_id". This nomenclature will make the intent of your SQL clear, something your successor will appreciate ;-)
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Finally, there is software that can "sniff" your network and create charts of the computers, their software inventory, etc.,; you might look on the 'net for examples (my mind is blank on this subject - no coffee yet this AM, sorry).
wjhutchinsAuthor Commented:
OS and Office are only concerns right now, in the future I might add the other software we use. but its not really necessary for this list as all other work related software is controlled through GPO's.

Didnt think about the M:M relationship i like idea of that table and take it one step further for user_location.

the multi hdd is not a big issue as this is more for the PC... but should be worked out bc all servers have multi hdd.

half our systems are dell incorporating a service tag will help, maybe ill add a build type.

wjhutchinsAuthor Commented:
Thank you for input on naming nomenclature, I was not aware. Will be applied when writing SQL.

I agree to an extent about meaningful names. but look up is so much easier.  Ive replaced user name with userID which will never change and can be linked in using exchange(hopefully). ComputerName will never change so not worried about that. Currently all systems are named after user but i will be renaming them systematical with WCT001, WCT002... Servers will keep their current names bc i don't feel like re pointing all the software and fixing all the random hacks and code that my 3 predecessors have written and deployed.

Haven't looked to much into automated software... but if it cost anything I'm sure it will be denied. The IT budget is.... well their isn't one. I will be using Speccy to aid me its a great little utility that will collect ALL available system information. Can be saved as a XML, TXT, or Proprietary file for later viewing.

Was think of trying to write a small program to sort through XML or TXT so info can be automatically compiled into a excel then uploaded to SQL DB. But its prolly a little out of my skill level.
Considering the table name is user, the PK name of userid seems redundant:

SELECT userid FROM user...

IMHO, of course ;-)
wjhutchinsAuthor Commented:
Total unrelated but why cant old people understand the right technology investments can pay for themselves in one year..... No matter how I explain it they don't understand.
wjhutchinsAuthor Commented:
Agreed will apply changes to userID upon db creation
Jeffrey CoachmanMIS LiasonCommented:
<why cant old people understand the right technology investments can pay for themselves in one year>

Because these people have been around for years, and they know that in reality, many things that claim to "Pay for themselves", ...rarely do.

Once you factor in the cost of setup, Taxes, Maintenance, the increase in the variable costs, ...etc, many things (like Hybrid car gas mileage, Solar energy, High efficiency appliances, ...etc) rarely *Actually* pay for themselves.

For example, you always see what things will save you in the "Future".
But I have *NEVER* seen and actual data that demonstrates the validity of these clams *After* the fact.

(Even more so, I have never actually seen any of the calculations used to determine these "Savings" in the first place)

If the Power company say that Conversion to Solar will pay for itself in ten years, then they need to actually show you the amortization schedule that includes: increases in fuel prices/electric rates, allowances for inflation, *True* startup costs, Cost of Maintenance, Cost of repairs, Insurance costs, warranty costs, etc...)
People love to say that their utility costs are cut in half, but they never mention (or factor in)  the 25,000 initial cost...

So when you say that something will "pay for itself in a year", show me the amortization schedule and "All" the costs...
Then show me the "actual" savings (based on all the "Real" costs) a year later...


Tom HopeProgrammer Analyst / ConsultantCommented:
A couple of other suggestions -
 Dell computers usually/always(?) have a service tag number with which they are associated. That would make a good id for the computer and provide you with a way to retrieve the original computer configuration from the Dell site.
 The non-Dell computers can have a similar ID. The only caveat is that the field needs to be sized and typed (15 - 20 text characters) so that there is room for growth.

Another suggestion is to add a computer type (desktop, laptop, server, workstation - whatever fits the environment). This can make searching for a specific system a bit quicker and easier.

You also might want to consider how much information you need to keep on each computer. For example, servers rarely need video card or driver updates whereas workstations used in design may be updated one or more times over their lifetime.
No worries - glad to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now