ASP.NET, SQL Server, WPF, C#, VB.NET

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

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 
 
* 
 
D. M. wrote:

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 
 
D. M. wrote: 
 
-- 
 
Message posted via AccessMonster.com 
 
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200608/1