fix Northwind 1. Relationships Diagram
Channel: LearnAccessByCrystal
Date: 2021-06-13

What's wrong with Northwind? Let's fix it! This is part 1 -- we're starting with the relationships diagram. Northwind Traders is a fictitious company created by Microsoft to showcase features in Microsoft Access.  It's great for ideas, but is just a starting point. Let's find out what to do to make this a serious application for business use.

When the Relationships Diagram is properly laid out, we can learn a lot. When rearranging, resizing, and repositioning, I generally follow these simple rules:

 1. Stretch or Shrink fieldlists to show names.
 2. Position "1" table on left and "∞" table on right.
 3. Put key fields at the top of fieldlists, order to minimize line crossings
 4. Show all tables on the diagram

~~~
Problem names and reserved words in Access by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html

Quick Access Toolbar (QAT) -- Customize and Share (Import/Export), Microsoft Access
https://www.youtube.com/watch?v=I_DCvlt8tYo

Tom van Stiphout -- presentation and code to  change names in Northwind
Change all object names, by Tom van Stiphout - Access Lunchtime
https://www.youtube.com/watch?v=NISiIYZi0rE

~~~
0:05 Relationships Diagram in Northwind is a mess.
0:10 There's lots wrong with Northwind -- let's fix it. We'll start with the Relationships Diagram.
0:24 Get Northwind for yourself
1:21 press F11 to go to Navigation Pane
1:25 change Navigation Pane to see by Object Type.
1:46 Tables, Queries, Forms, Reports, Macros, and Modules
1:53 Expand the table names.
1:56 Open the Relationships window.
2:00 customize your QAT
2:18 Northwind Traders is a fictitious company created by Microsoft to demonstrate features of Access.
2:38 The Relationships Diagram should be a birds-eye view of the database.
2:41 Rearrange the Relationships Diagram and find out what it can tell us.
3:00 1. Stretch or Shrink fieldlists to show names.
3:06 2. Position '1' table on left and 'many' table on right
3:17 Collapse the Navigation Pane for more screen space.
3:23 The 2 main resources of this company are Employees and Products.
3:36 The diagram flows from left to right.
3:43 Names shouldn't have spaces.
3:56 PRIVILEGES is a reserved word.
4:01 Allen Browne's reserved word page
5:16 What's wrong with this design?
5:41 The Primary Key shouldn't be called 'ID' in many tables.
5:56 'ID' all by itself is ambiguous.
6:06 When field names are the same and they DON'T relate, problems can happen.
6:26 The fields in these tables have the exact same names; that's not a good thing to do.
6:33 Shorten 3 of the fieldlists with redundant data structures.
6:49 Attachments should be attachments! so, not stored here, but accessible.
7:00 Attachments can't directly convert to many other data repositories.
7:54 Change Access options to show Overlapping Windows instead of Tabbed Documents.
8:05 In most cases, disable AutoCorrect.
9:30 Show Quick Access Toolbar Above (or Below) the Ribbon
9:45 What is the maximum number of characters for a name?
10:16 the maximum number of characters allowed in a name is 64.
10:22 I think 25 characters is way too long!
"10:28 Names should be concise yet descriptive. 
And not have spaces or special characters! 
Underscore _ is ok, but not for the first character."
10:41 Fieldlists are sized to see everything, generally.
10:58 Then invoices are generated, products are shipped, and payments are made.
11:05 This structure isn't very good.
11:13 The Northwhind template can be a good place to START
11:24 When you see a '1', on a link line that looks like '11', reposition fieldlists.
11:48 Enforcing referential integrity causes link lines to be labeled with '1' and/or '∞' on the ends.
12:08 3. Put key fields at the top of fieldlists in whatever order minimizes the link line crossings.
12:54 Transaction Type is a foreign key and defined with a RowSource
13:05 I like to name long integer key fields with ID on the end.
14:03 The Products table has a multi-value field -- to show that Access can do it.
14:40 Templates showcase features of Access, not the best database design.
14:54 cross-reference table
15:23 Northwind Traders gets its products from Suppliers using Purchase Orders with a details table
15:36 When an order is being placed to a supplier, inventory transaction records can be created from the customer orders.
15:59 The Orders table keeps track of the Customer, OrderDate, who will ship it, and where it will be shipped to.
16:16 The Relationships Diagram gives us a great way to see what's going on.
16:42 Category is a lookup field in Products -- it should be a foreign key and have its own table.
17:14 Poor performance with subdatasheets
18:38  4. Show all tables on the diagram.
19:39 screen shot of relationships diagram
20:28 Add missing tables
21:11 Tom van Stiphout wrote code to remove spaces and funny characters of names in Northwind.

Visit MsAccessGurus for free code, tools, articles, videos, and remote training 
http://www.msaccessgurus.com

Please comment, thanks.

kind regards,
crystal