Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-09-2019, 03:42 AM   #1
ColdPenguin
Newly Registered User
 
Join Date: Jul 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
ColdPenguin is on a distinguished road
Talking Anything wrong with my table relationship design?

This is my first shot at creating a practice database. Ideally, I wanted each table to only reference one other table, but the Visit table ended up hosting 4 foreign keys.

At the same time, that's kinda useful, because I don't have to add irrelevant tables in order to get to the association between two tables I want to query.

I also haven't figured out what table to connect with the Expense table.

In any case, I'm a noob, and if anyone wants to practice their database analyzation--or just enjoy dominating a noob, haha, come at me.

Had to zip the file, as apparently without 10 posts here you can't attach non-zipped files.
Attached Files
File Type: zip Microsoft Access 2016 Project.zip (43.0 KB, 21 views)

ColdPenguin is offline   Reply With Quote
Old 07-09-2019, 05:28 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Anything wrong with my table relationship design?

Welcome to AWF !

Looks okay at first glance to me, but your expense table isn't right.

You should probably have
ExpenseID
ExpDate
ExpenseType - This would indicate Building, Materials, Weapons or other you might add.
ExpValue

Otherwise you have gone all spreadsheet on us.

If you wanted to you could try and link the expense back to a specific weapon or material ID, but because they are appear to be separate entities it's a bit messy.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-09-2019, 09:44 AM   #3
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,125
Thanks: 3
Thanked 462 Times in 455 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Anything wrong with my table relationship design?

Some more comments.


I'd not have the underscores in field names - just more typing and using CamelCase easily distinguishes words, just as you have with the table names.


ExamPassed in tblLegal should probably be based on a lookup table.


The prices in tblWeapons and tblMaterials. Will these change over time? And if you want to record the prices at the VisitDate, these fields would need to be also included int tblVisit.

Cronk is offline   Reply With Quote
Old 07-09-2019, 12:05 PM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,038
Thanks: 78
Thanked 1,996 Times in 1,944 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Anything wrong with my table relationship design?

There are free tutorials on relational database at RogersAccesLibrary.
For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials from RogersAccessLibrary.
ZYX Laboratories ***recently added
Class info system
Catering Business
Widgets
Work through
1 or 2 and you will understand relationships and table design.
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 07-09-2019 at 01:35 PM. Reason: forgot to post the links
jdraw is offline   Reply With Quote
Old 07-09-2019, 12:50 PM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Anything wrong with my table relationship design?

One thing you may want to consider; Any chance a customer will have more than one email or phone number?

I'd add a "tblCustContact" that has
ContactID - AutoNumber
CustomerID - Foreign Key to link to the customer
ContactType - Email, Phone, IM handle, what have you
ContactDetail - The actual Email address/number/what have you.

OPTIONALLY you can include date/time when they can be reached by this method or other details, as needed. It does avoid issues when a customer wants to give you their cell phone, but then remembers they never answer it while at work and want to give you their work number (and extension) to.
Mark_ is offline   Reply With Quote
Old 07-09-2019, 11:53 PM   #6
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,168
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Anything wrong with my table relationship design?

I thought this may be of interest.

I demonstrate the correct way of storing multiple telephone numbers, emails, (any communication string) in my blog here:-

https://www.niftyaccess.com/excel-in...-instructions/

Demonstrates how to convert horizontally stored information, eg; information stored in an individual field like home phone, work phone, mobile phone, home email, work email etc. .... Extract that information from a table, (very likely a table imported from Excel) and place it into a new table with essentially three columns, an "ID" column, an identifying column which identifies it as a email phone number etc, and then the actual string necessary for the communication.

The Tool for transposing this data is free if you sign up to my newsletter.



Sent from my Pixel 3a using Tapatalk

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Reply

Tags
foreign keys , table relationships

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table design and relationship prmitchell Tables 2 06-20-2013 05:27 AM
Table and Relationship design help Cwittmaack Tables 11 01-28-2013 01:39 PM
Simple relationship table design, Why ID numbers? thebatfink Tables 6 09-08-2012 09:12 PM
Table \ Relationship Design SteveGMC Tables 7 10-07-2010 10:39 AM
Table Design and Relationship and Data Form MrDix Tables 3 04-29-2005 06:06 AM




All times are GMT -8. The time now is 07:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World