The question of storing data that should have been in the same table
The software I work with has 2 tables, a manual and a client. When we sell a product to someone, a record is created in the customer table with data from the master table (as well as some additional data).
There is currently no relationship between the two tables. The best thing that exists now is the main entity that will search the customer table for an entry with the same phone number (hoping it hasn't changed in the last 5-10 years that they were our customer - doing such a search on each output results in an intersection of% 82 on the client's table). No reverse lookup (customer-> lead).
The problem is that I need to know which customer record is associated with the record and vice versa.
I thought about storing fk from master in client and fk from client in the manual ... but the ORM I'm using will overflow as it loads related records when fkeys exist.
Concatenating the two tables into a "person" table is what I would like to do, having a flag bit to determine if the person is a customer ... but alas, timelines and budgets don't allow it.
I'm not quite sure what to do.
a source to share
Do you really need to go both ways (i.e. 1: M leads to customers and 1: M customers)? If so, then a composite table sitting "between" the two may be the way to go. Each entry will contain a PC from the client and a PC from the lead (linking the two).
If you just need to know how many links are associated with a client, I would add FK to the pins (pointing to the PC in clients).
a source to share
You will need an ID column in each of the two tables to uniquely identify your records (if you don't already have one).
And you need to add another link table (joins) to connect existing tables. This table will contain two columns: LeadID and CustomerID. Each row contains the identifiers of the corresponding rows in the Customer and Lead tables.
Foreign keys will link your tables: LeadID -> ID column in Lead table; CustomerID -> ID in Customer table.
a source to share