Relationship Questions

[Guest]

This is really made a mess. I'm working on "fixing" a lease management
database, (which at this point is just used to house data). There are many
tables in the database. But the main table is called tblStores.

The fields are:

StoreID
StoreNo
Address1
Address2
City
State-Province
Zip-PostalCode
Country
Region
Status (drop down box negotiating, approved, open and closed)

I then have a table housing lease information such as, commencement date,
expiry date, term, etc., etc.

Each table has its own ID (StoreID, LeaseID, PaymentID, etc.)

I'm really confused as to how these should relate. I believe that whoever
created this database really got it backwards. Each of the tables has the
storeID as a secondary primary key and the relationships are all based on the
StoreID.

But that doesn't work. Can anyone help?

Thanks

 

[jahoobob via AccessMonster.com]

The creato didn't get it backwards, he just had it mapped incorrectly for
what needs to be done.
The Lease table should probably be linked to the Stores table via the StoreID
foreign key ((secondary key as you call it.) BTW, if the StoreNo is unique
then the StoreID is not needed and the StoreNo should be the primary key but
it is okay to leave it the way you have it. The Payments table should be
linked to the Lease table by the LeaseID via a LeaseID foreign key. This is
because you can have many leases for one store and then each of those leases
can have many payments. If you tie the Payment to the store you won't be able
to tell to which lease a payment should be credited.
Also, you probably don't want to have a lookup field in your table, use a
combo box on a form to enter the Status data.
Hopethis helps,
Bob

 

[strive4peace]

When you set up your tables, each is like a noun and then the fields are
adjectives that describe it.

Make sure each table does have an Autonumber field...StoreID, LeaseID,
PaymentID

So, your tables would be something like: Stores, Leases, Transactions
(Payments and Costs)

just guessing since you didn't specify...
A lease can cover many stores
A store can have more than one lease
A customer can go to any store
Transactions are allocated to (1) store (2) customer

Then, when you have described each table, you need to figure out how
they relate to each other and put linking fields into place.

To join Leases and Stores, since this is a many-to-many relationship,
you can use a linking table, such as StoreLeases with:

StLeaseID, autonumber
StoreID, long integer
LeaseID, long integer

Transactions would probably have a StoreID in the transaction record as
well as a CustomerID, ItemID (what it was for), etc

You would need a table to keep track of your lease companies too -- put
all company information into one table, such as Companies, and have a
category field for type or company as opposed to just a table for
LeaseCompanies


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*