0

I think I'm confusing myself and making this too complicated. At this point, I need outside advice.

I have a Laravel 7 application where a user will enter orders. Each order has one customer. Each order has one contact. Each customer has many contacts, but contacts only belong to one customer.

When the user is entering the order and chooses a customer, another select field populates with contacts associated with that customer.

Here is my structure:

orders table: id customer_id contact_id

Customers table: id name

Contacts table: id customer_id

Is this good or do I need to change the structure? I'm having trouble setting up the relationships in Laravel.

  • as long no coustmer_id a shared by multiple names, or vice versa you don't need that complicated structure – nbk Jul 16 at 16:41
0

I'm not familiar enough with Laravel 7 to understand how well it implements alternate keys/composite keys, but this is basically what you've described: enter image description here

Notes:

  1. The alternate keys are crucial as an Id in and of itself does not provide uniqueness as required by the Relational Model. If I can spam "Customer 1, Dwayne Johnson" a million times into Contact then no key exists and there is a normalization error.
  2. The composite key (CustomerId, ContactId) is required to ensure CustomerId is migrated properly to Order
  3. IE is inversion entry, or non-unique index. This allows orders to be quickly searched by customer.
  4. ContactId itself is not unique. If you are using a ORM that requires an Id column on everything, you would need to make the relationship between Customer and Contact non-identifying. In doing so you will remove the ability to retrieve a customer's orders without joining to Contact first. Under no circumstances should you create a relation between Customer and Order as the Contact selected must belong to the Customer in question and this would introduce a normalization error.

Revised model based on comment:

enter image description here

| improve this answer | |
  • Hi, bbaird. The contacts table has id, which is unique, and customer_id, which is the foreign key. It sounds like you are saying I should be removing the contact_id reference in the orders table. Is that correct? How would I store which contact is associated with the order? Do I create a pivot table? – ajsmith Jul 16 at 18:34
  • @ajsmith, please see updated diagram. – bbaird Jul 16 at 18:41
  • 1
    I think I understand. Thank you for taking the time. :) – ajsmith Jul 16 at 18:42
  • @ajsmith to find which customer placed the order, you will need to join back to Contact in order to get the CustomerId. This is a disadvantage of the second model. Note the alternate key (CustomerId, Name) does not change. – bbaird Jul 16 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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