Solved

Database normalization

Posted on 2010-11-24
12
1,631 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
12 Comments
 
LVL 142

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now