A question about storing data that should have been in the same table

Question

The software I'm working with has 2 tables, lead and customer. When we sell our product to someone, a record is created in the customer table with data from the lead table (as well as some additional data).

Currently there is no relationship between the two tables. The best that exists now is the lead object has a function that will do a lookup in the customer table for a record with the same phone number (hoping that it hasn't changed in the last 5-10 years that they've been our customer - doing such a search on each lead produces an %82 intersection on the customer table). There is no reverse lookup (customer->lead).

The problem is that I do need to know what customer record is associated with what lead record, and vice versa.

I considered storing a fk from lead in customer and a fk from customer in lead... but the ORM i'm using will overflow as it loads associated records when fkeys exist.

Merging the two tables into one 'person' table is what I'd like to do, having a bit flag identifying whether or not a person is a customer... but alas, timelines and budgets do not permit.

I don't quite know what to do.

Answer 1

Do you really need it to go both directions (i.e. 1:M leads to customers and 1:M customers to leads)? If so, a composite table sitting "between" the two could be the way to go. Each record would contain the PK from customer and the PK from lead (linking the two).

If you just need to know how many leads are linked to a customer, I would add the FK to leads (pointing to the PK in customers).

Answer 2

You will need the ID column in each of your two tables to uniquely identify your records (if you don't have it yet).

And you have to add one more linking (junction) table to connect existing tables. This table will contain two columns: LeadID and CustomerID. Each row contains IDs of corresponding rows in Customer and Lead tables.

Foreign keys will connect your tables: LeadID -> ID column in Lead table; CustomerID -> ID column in Customer table.