0

I have a User table having following fields:

id, name, phone, email

and an Order table:

id, order_name, order_desc, user_id (fk to User)

and a Lead table:

id, lead_desc, user_id (fk to User), lead_status

Now, I want to know for each lead (once the lead_status become closed), which order is created from this lead or no order is created from this lead. I am planning to have an order_id (with null=True) foreign key in Lead table to keep this mapping. But since Order table already have a user_id, it doesn't seem to be a good idea.

Is there a better way to do it? I am using Mysql.

2
  • Is the user_id in the Order entry and the corresponding Lead entry always the same User?
    – Marco
    Commented Aug 17, 2016 at 13:11
  • @Marco Yes. It will be same.
    – Anuj
    Commented Aug 17, 2016 at 13:36

2 Answers 2

0

One of the of goals of a relational database is to eliminate double storage of data. If you can find the right User for the Lead through the Order then you can leave out the reference in one of the tables (they are always the same as you mentioned in a comment). Keeping it in both tables makes your application more complicated.

2
  • I understand that but Lead record is created first and then Order is created. Also, Order can be created independently.
    – Anuj
    Commented Aug 17, 2016 at 16:04
  • From your question there is no real indication of the use of Lead. From your last remark I make up that there need not be a 'link' all the time between a Lead and an Order. In that case you need to keep the user_id in both tables.
    – Marco
    Commented Aug 18, 2016 at 9:01
0

As per my understanding I have given the following points, it may change as per your requirements.

Business Rule:

  1. Each user can have more than one Lead inquires.
  2. Each Lead must be belongs to at least one User.
  3. Each Lead can contain one or more Products/Services.
  4. Each Product/Service can belongs to one or more Lead.
  5. A User can place more than one Order.
  6. Each Order must belongs to one User.
  7. Each Order can contain one or more Products/Services.
  8. Each Product/Service may belongs to more than Order.
  9. Each Order must contain to atleast one Lead.
  10. Each Lead may belongs to atleast one Order.

Data Model:

For the above mentioned business rules, the logical data model may look like as follows:

User-Lead-Order Logical Data Model

In my opinion, if you need to capture and report key performance indicators (KPI) of the business, you need to clearly define the relationship between the Lead and Order. Usually, the business flow will be, first Lead created and Lead may / may not converted into an Order, in such cases, you can store the Lead_ID in Order table, so that all the Order will have Lead_ID and note that, reverse it may not possible, since all leads may not converted into Order.

Also, there is no clarity that, whether all the order must comes only through leads. Before starting anything, make sure to clearly define the entities. I suggest you to always start with conceptual data model to physical table design. As per your requirement, the design may change. I hope this answer will help you, Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.