Structure Flaws? (1 Viewer)

[browninaz]

Hello all,

Can somone out there in the world of programming help this amateur out?

I have been having a problem with creating queries, forms and reports, and was wondering if the structure of my database was in the correct order before I ask too many more stupid rookie questions...

I have attached the layout and relationships of my tables, can someone take a look and make sure I'm on the right path?

Thanks a bunch for any assistance.

 

[jdraw]

It is difficult to say if your Relationships are appropriate. You haven't told us anything about your "business" -- rules/facts.

Look at some of the data models here
http://www.databaseanswers.org/data_models/index.htm

and then look at the Business Facts/rules/specifications. There is usually a link at the top of the model.

Here's a sample set of facts
http://www.databaseanswers.org/data_models/car_hire/business_rules.htm

for this data model
http://www.databaseanswers.org/data_models/car_hire/index.htm

If you have a list of facts, or a description of your business, perhaps someone will have comments specific to your model.

Good luck with your project.

 

[browninaz]

Thanks, jdraw. I own a floor covering retail business, and I sell and provide products as well as installation services. The workflow starts with the customer, which has the choice to purchase materials and install the materials themselves, or purchase the materials and hire us to do the install for them. The contract is universal, purchase materials only materials with installation. From the contract comes all the information pertaining to the sales or sales and installation of the products. I had a real mess going with the relationships between the tables, which was causing a problem with queries, forms and reports, so I restructured the design of the whole database. I was using this forum to ask questions that couldn't be fixed without cleaning up the structure first, and wasting everyones time.

I now see a flow with the way that my relationships are estabilished, but one of the main questions I have is, should I join my contract table to the products table in order to create a multi table query on any of the tables, or do I use the customer table as the "junction table" (if I'm using the term correctly) to create my queries?

Is that enough info?

 

[jdraw]

There's a bunch of info I often recommend. My guess is you have done many of the things identified in these materials. I'll list the links below.

But the "correct" relationships are those that serve your particular business.

A junction table is used (primarily) to resolve Many to Many situations.
eg Students take Courses.
There's a video here https://www.youtube.com/watch?v=7XstSSyG8fw that shows junction table.

Here's the material

http://www.rogersaccesslibrary.com/forum/topic238.html Lots of design info

Here are a number of free video tutorials related to database design concepts.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

 

[browninaz]

Thank you so much jdraw. I will dig into these links in a little while and let you know how it works...

 

[jdraw]

Good luck. You can stop and repeat some of the Langer videos to emphasize/clarify things.
The video quality isn't the best, but the info is good, and it's 1 presenter on the same subject matter throughout the videos.

The Entity relationship Diagramming and 12 Steps to better databases at the Roger Carlson site are very good and have examples in those documents.

 

[aadhirasharma]

Thanks for the link.