Forgive me for my dumb question but I am new to Ruby on Rails and data design. We all have to start somewhere right? Well my question is that I want to build a database for a Truck driving company. It consist of Drivers, Brokers, Invoices, Destinations, and type of produce delivered. I have attached a screenshot so you can see the relationships on the table.

My question is that in the invoice table "Start_Destination" and "End_Destination" each has a one relationship to the "Destination_Company_Name" in the Destination table. Is this okay? Like two fields on one table tied to one field on another? The same goes for the "Produce_type", you will see that it is 3 times in the invoice side but only one joint on the Produce table. Is this okay?

Any advice would be great!! and again sorry for being such a newbie on this but can't wrap my mind on a different way. enter image description here

  • 1
    Don't put arrays (First_produce, etc) in columns; use a separate table. – Rick James Dec 27 '15 at 3:59
  • Check out this site for sample schemas - you could adapt one for your needs. – Vérace Dec 27 '15 at 20:14

Nothing wrong with "destination".
And what about produce types. Is it always 3 produce types for every
record in "Invoice" table? Why are their three(not five or ten, for example)?
Is the count of produce types varying from record to record in "Invoice" table?

| improve this answer | |
  • Thanks for the comment jvb! It will depend. Sometimes they might take just one sometimes 3. They usually will not take more than 3. Are you thinking that I might add more? – Julio Contreras Dec 28 '15 at 1:37

It should be fine to join like this but I think you could do it without multiple links. If a Destination can only be "Start" or "End" why not add a boolean field "Destination_Start" and then have one link to a destination table.

Also you could reduce those three links to produce and make it easier to add more types of produce by having a field in invoice also called produce type. This could be indexed. I'd recommend you put a Foreign key in invoice for this and make the invoice to produce relationship one to many (So one invoice can have multiple products).

| improve this answer | |

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.