Anything wrong with my table relationship design? (1 Viewer)

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.

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.

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.

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.

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.

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-access-parts-1-2-and-3-as-video-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