Relationships cardinality and . . (Newbie)

  • Thread starter Thread starter Lionel
  • Start date Start date
L

Lionel

Hi all,

I'm creating a database in access and I am having some troubles. When I create
relationships Access is defaulting to the wrong cardinality ration, how do I
change this?

My next question I will ask by an example:

Say we had a table customer. An instance of this table with name Bob (call it
bob from now). Now bob owns a car, this car can be either a sports car, a family
car or a small car, but only one of these. The Customer table obviously has
relationships with each of a SportsCar, FamilyCar, and SmallCar table. My
question is, how do I represent that an instance of Customer has a relationship
to exactly one of these?

Thanks heaps for your time,

Lionel.
 
Hi,
First question - click on the relationship line. It will bring up a dialog
so you can determine which of 3 relationships is the most appropriate.

Second question

tblCarType
CarTypeId
CarTypeDes

tblCustomer
CustId
other cust details
CarTypeId

Then drag and drop cartypeid from tblCarType to tblCustomer on cartypeid.
And the relationship should be built correctly.

Marc
 
If by "wrong cardinality" you mean you want to create a One-to-Many, but it
is creating a One-to-One, then you have a unique index on BOTH of the
linking fields. By definition, a 1:M relationship must have a unique index
on the field in the "One-side" table (this is usually the Primary key, but
does not have to be) and NO unique index on the linking field in the
"Many-side" table.

If, on the other hand, it creates a One-to-Many but in the *wrong
direction*, then you have not created you foreign key correctly. To create
a 1:M correctly, you need to take the PrimaryKey field (usually, as I said)
from the "One-side" table and put it in the "Many-side" table as the foreign
key and create the relationship on those two fields. If it is creating it
in the wrong direction, you have placed the Primary key field of the
"many-side" table into the "one-side" table.

As for your second question, there should only be ONE car table, with a
field which differentiates the car type. This eliminates your problem.

--
--Roger Carlson
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks for the help btw. I ended up having to use this option, I am to used to
OO and was looking for something where I could reduce the null values a bit
more, but in the end I think it is not possible to do it the way I wanted to.

Lionel.
 
Back
Top