Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 26th, 2004, 04:43 PM
Authorized User
 
Join Date: Aug 2004
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default Database Normalization

I am creating a database to keep track of our printers. We have five different mainframe systems each with their own unique system id. There are over 100 printers that are configured on each mainframe and some of the same printers are configured on multiple systems. I am not sure if I should create a table for each individual system id and list the printers on that particular system or if I should put them all in one table. Can someone [u]PLEASE</u> help me with this issue? Thanks.
 
Old August 26th, 2004, 08:16 PM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Teqlump,

It seems that have two objects: Mainframe systems and Printers theses make up your entity tables. I assume that each printer has a unique identifier so key (PK - Primary Key) both tables on their respective unique identifiers. Call them what you like but for reference in this post I'll name the tables 't_Mainframe' and 't_Printer' and the PK attribute names 'mainframe_id' and 'printer_id'.

By your description one mainframe can have many printers and one printer can have many mainframes. This is a many-to-many relationship and requires a link table. There are only two attributes in this table. The first attribute is the unique identifier of the mainframe and the second attribute is the unique identifier of the printer. Since this is a link table I've called it 't_Mainframe_Printer'. I would suggest creating a unique composite key using both fields.

Create two relationships. The first is a one-to-many relationship from 'mainframe_id' of 't_Mainframe' to 'mainframe_id' of 't_Mainframe_Printer'. The second is a one-to-many relationship from 'printer_id' of 't_Printer' to 'printer_id' of 't_Mainframe_Printer'.

Play with a few views across the three tables and you'll see how the data links.
Hope this helps.

 
Old August 26th, 2004, 08:25 PM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry forgot it was Access so where the word 'views' is used in the last sentence, replace with 'queries'

 
Old August 27th, 2004, 01:41 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have you populated the link table with the relevant data?
eg 3 Mainframes and 5 Printers

table
t_Mainframe_Printer

attributes (fields)
mainframe_id printer_id
m1 p1
m1 p2
m2 p1
m2 p2
m2 p3
m3 p4
m3 p5

From this setup:
Mainframe m1 has two printers p1 and p2 connected to it
Mainframe m2 has three printers p1, p2 and p3
Mainframe m3 has two printers p4 and p5

Working the other way:
Printer p1 is connected to Mainframes m1 and m2
Printer p2 is connected to Mainframes m1 and m2
Printer p3 is connected to only to Mainframe m2
Printers p4 and p5 are only connected to Mainframe m3

If not problem explain what process you followed and re-post and I'll be glad to have another go.
 
Old August 27th, 2004, 01:49 AM
Authorized User
 
Join Date: Aug 2004
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

We have five systems, for this example I will just use System A thru System E. The table I have created is called System A. In this table I have System ID and Device number. I am having a hard time figuring this out. The table I just mentioned is set up as follows:
[u]Device #</u> [u]System ID</u>
LP234AD H010
LP234AR H010
LP234AD H010
LP234AE H010

You see how H010 is listed 4 times. This creates redundancy and I need a primary key to join from the primary table to the junction table. I have tried this by making a table with only the 5 system names and using the table above. This is really killing me. How can I make this work? I really need some assistance with this.

 
Old August 27th, 2004, 02:10 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What you have there is the basis for a good link table.

It's good practice not to assign a specific table to a specific object, however in certain circumstances it might be benficial. I'll assume that it's not one of those cases and we are building a relational database.

The system ID repeated in a table link this is not redundant data.

Create a table called System. Create this table with two basic fields (more if you need to). system_id and system_description. Populate the system_id with the unique identifier and a description for each of the five systems A-E.
Example:
Tablename: System
System ID, System Description
H010, System A
H???, System B
H???, System C etc

Do the same for the printers.

Tablename: Device
Device #, Device Description
LP234AD, Printer D
LP234AR, Printer R
LP234AE, Printer E

Now create the link table in the same manner as your example System A table. (If you create the PK across the two fields you will avoid the duplicate record situation found for LP234AD in your example)

(Note also that this example is not as normalised as you can go. If your only concern is for printers then this is fine but even better would be to create a device type table and linking your device table to the device type. That way you could track other peripherals as well. You might even consider doing it for the system table. As you keep thinking about you'll probably think of more, arrgghhh! Normalisation, when does it stop!)



 
Old August 30th, 2004, 09:51 PM
Authorized User
 
Join Date: Aug 2004
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've got it working, however there is one small issue that may mean it's still set up wrong. The relationships are set up between the three tables: tblPrinters:(Fields: Printer ID, Printer Type, IP Address...Printer ID is the PK) tblSystems:(Fields:System ID & System Name...System ID is the PK) and tblIntermediate:(Fields=System ID & Printer ID both PK'd) The problem, well it might not be a problem, is that I can't populate the intermediate table with an append query. If I do try with an append query, it will list all five system id's with all of the printers and I don't want it to do that. Please let me know if you understand so far. If intermediate tables cannot be updated with an append query, is this poor database design. By the way, thanks for your help the other day Chapster. I appreciate it. :)
 
Old September 1st, 2004, 04:32 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi there,

I assume you have an electronic list of all the printers for each system? If you don't then you may have to either run the append query you created and delete the unnecessary entries or manually enter all the correct entries (The less work the better).

If you've got a list then all you have to do is reproduce this list in a spreadsheet with the printers in column B and the system identifier in column A. Just use a fill down function to populate the system id column then import this into the Access table.

Good luck!











Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem to restore database in C#2005 database acmuralee MySQL 0 March 25th, 2008 04:42 AM
Microsoft JET Database Database Engine (0x80040E09 cannielynn0312 Classic ASP Professional 2 December 17th, 2007 02:50 AM
Speed and Normalization foddie MySQL 5 January 18th, 2006 01:39 PM
Normalization and comparisons mega Excel VBA 3 March 7th, 2005 02:05 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.