Can someone give me hand? I'm tryi

[StanKorn]

Can someone give me hand?
I'm trying to create a DB that will provide leaseholder info on transactions. The problem is there may be more than one lease holder associated with an account. (At most there are two lease holders tied to an account)I need to be able to search on field LastName in tenant master in order to determine the lease number associated with the tenant making the payment. There can only be one lease # associated with the tenants but there may be more than one tenant tied to the lease.
I have created table with LastName, FirstName & TenantLease# with no primary key which allows me to search for the Lease associated with the name; but I must then feed the lease # into a tenant master that is driven by lease #.
My problem is how do I feed the unique lease # into the Master table when it is not unique in the source table without creating duplicate records and bombing the app?

 

[KornGeek]

I recommend have a Lease table that uses LeaseID as the primary key. This will serve as your master lease table and prevent duplicate lease ID's.

Next, I wouldn't assume that all tenants can have at most one lease. Once you assume this, it is almost guaranteed that somebody will open a second lease within a week of the database completion. The world just seems to work this way (for me at least).

To accomodate this, I would create a Tenants table that contains TenantID (the primary key), LastName, FirstName, etc.

To tie the tenants to the leases, you create a table in between that serves to create a many-to-many relationship. This table would be a mapping of TenantID to LeaseID with the combination of these two fields would be the primary key of this table.

This allows you to look up all tenants for a lease, and all leases for a tenant using some simple select queries.

This should get you started. Let us know if you need further help.

 

[StanKorn]

Thanks for the info. I'll attack it from that perspective