How to create table with relationship..?

[khalakmu]

Sorry to post as new thread, I have asked question before so please
accept my apologies.
I have created Database in Access 2007 MyCollection with following tables..

1. tblPostalIssues (PostalIsuID pk auto, Desc,Subject)
2. tblStamps (StampID pk auto, PostalIsuID fk,other fields)
3. tblSpecs (SpecID pk auto, other fields)
4. tblVarieties (VarietyID pk auto,SpoecID fk,VarietyType)
Relations...

tblPostalIssues 1:M tblStamps
tblStamps 1:M tblSpecs

Now my dilema is Specifications are PostalIssues as well as Stamps
PostalIssues Specs are as the Issue Authority Declared most of the time (some time not)
Stamps Specs some stamp may have same specs as Issued Issue. But other might have
differrent specs either one of the specs or more that one Specs different.

How I should relate Table Varieties...
Should I take Fk as StampID or SpecID, Or stampId and SpecID also
Should I put y/n field in tblSpecs ( Both specs Same, Specs Deffers)

Also These Varieties are categorize as ERRORS which are Major errors and
other Defferences as EFO as 1.Error minor 2.Freak 3. Oddities on defferent
Crieteria of Specs and Definitions are Ildefined.

Can some body guide me or advise me.
Attached is the screenshot of Relationships.

View attachment DBV20Screen.zip

Thanks in advance.

Kahalkmu

 

[jdraw]

The only suggestion I could make is to review your structures. Are the Tables normalized? It appears you have a lot of knowledge re stamps and collecting -- probably more than the rest of us. So you, or another stamp collector/enthusiast, would be in the best position to review the structures.

Here are several free video tutorials on Normalization that may assist with concepts.
I have listed the videos in sequence

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

You can go back and watch these as often as you want.

 

[khalakmu]

Hi jdraw,

Many many thanks for providing links. I have some but I would not call lot knowlege
in stamp collecting. I am hobbyist. Have interest in different fields. Anyway I am so much
obliged for these links. I did have all list of attributes was trying to create Entities.

Just trying to create database for my hobby. One can call sort of reinventing.
Because there are Programs in the market available. They fail one or other aspect
of my Collection. I did put down diagram in partial as many Look up tables
did not include in the diagram. At least you answer my problem wait was well
worth.
Again thanks and regards.
Khalak

 

[jdraw]

I'm glad you found the videos worthwhile.
Here are a few more links that may help with database design.


This one is a bit of a read, but is a great reference
http://xa.yimg.com/kq/groups/20697041/1514891951/name/UnderstandingNormalization.pdf


There is a good rdbms and design article here
http://forums.aspfree.com/microsoft-access-help-18/relational-database-principles-208217.html
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

http://www.rogersaccesslibrary.com/Tutorials/The Hernandez Process In A Nutshell.doc

There are free existing data models as
www.databaseanswers.org but I saw nothing specific to stamps and collecting

Good luck with your pproject

 

[khalakmu]

I created tblLists but could not make proper relationships with other tables.
I do not know where am faltering.

khalak

 

[jdraw]

Did you read this http://www.rogersaccesslibrary.com/T...lationship.zip ? Have you tried going through some of the videos a second/third time?
Can you attach anything you have done? I have acc2003.

 

[khalakmu]

Hi jdraw,

Yes I have read. I can post again what I have done. I am stumbling in both
databases at the Fault (Errors) ,FaultType, Product (Item) with respect to
its Specified Specifications.
Normalization I understand very well I can say (if one table kept Unormalised.. Puposefully) that also I understand.

I will Post again thank you for answering I will Post Relationship Screen.

thanks again
Khalak

 

[InspiredGrowth]

great links, forum topic book marked!