Solved

Database normalization

Posted on 2010-11-24
12
1,672 Views
Last Modified: 2012-05-10
This will probably be an ongoing discussion any users with applied input will receive points

Task:
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...

Reason:
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

 CompDatabaseDesign.jpg

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

Thanks
 
0
Comment
Question by:wjhutchins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 34206121
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 ...

Computer_Users
----------------------------------
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?

0
 
LVL 29

Expert Comment

by:Badotz
ID: 34206451
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.
0
 
LVL 29

Assisted Solution

by:Badotz
Badotz earned 200 total points
ID: 34206469
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 ;-)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 29

Expert Comment

by:Badotz
ID: 34206487
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).
0
 

Author Comment

by:wjhutchins
ID: 34206498
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.

CompDatabaseDesign1.jpg
0
 

Author Comment

by:wjhutchins
ID: 34206778
Badotz:
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.
0
 
LVL 29

Assisted Solution

by:Badotz
Badotz earned 200 total points
ID: 34206865
Considering the table name is user, the PK name of userid seems redundant:

SELECT userid FROM user...

IMHO, of course ;-)
0
 

Author Comment

by:wjhutchins
ID: 34207034
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.
0
 

Author Comment

by:wjhutchins
ID: 34207042
Agreed will apply changes to userID upon db creation
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34207178
<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...


;-)


JeffCoachman
0
 

Assisted Solution

by:Tom Hope
Tom Hope earned 100 total points
ID: 34208486
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.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 34229569
No worries - glad to help.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question