Database normalization

Posted on 2010-11-24
Medium Priority
Last Modified: 2012-05-10
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

Question by:wjhutchins
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 800 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 ...

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?

LVL 29

Expert Comment

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.
LVL 29

Assisted Solution

Badotz earned 800 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 ;-)
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 29

Expert Comment

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).

Author Comment

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.


Author Comment

ID: 34206778
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.
LVL 29

Assisted Solution

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

SELECT userid FROM user...

IMHO, of course ;-)

Author Comment

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.

Author Comment

ID: 34207042
Agreed will apply changes to userID upon db creation
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...



Assisted Solution

by:Tom Hope
Tom Hope earned 400 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.
LVL 29

Expert Comment

ID: 34229569
No worries - glad to help.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

862 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