3

Hi I have two tables that has one to many relationship.

 Table 1 Users:                     

 -------------
| id  |  name |
 -------------
| 01  |  John |
 -------------
| 02  | Harry |
 -------------

  Table 2 Cars:                     

 -------------
| id  | Brand |
 -------------
| 01  |  GM   |
 -------------
| 02  | Honda |
 -------------

Now any user might have multiple cars. Which one would be faster and performance efficient?

Having a array columns named carOwned in User column:

 Table 1 Users:                     

 ------------------------
| id  |  name | carOwned |
 ------------------------
| 01  |  John | [01, 02 ]| 
 ------------------------
| 02  | Harry | [02]     |
 ------------------------

Or a lookup table like?

   Table 3 CarOwnership:                     

 -----------------
| userId  | carId |
 -----------------
| 01      |  01   |
 -----------------
| 01      |  02   |
 -----------------
| 02      |  02   |
 -----------------
  • 1
    From a pure database design perspective, I'd go with junction table similar to your last option. – Antoine Hernandez Feb 24 '16 at 19:20
  • 1
    Beyond a shadow of a doubt, I agree with @SoleDBAGuy - the second option is the way to go. It obeys Codd's rules (arrays don't) and it's standard (i.e. portable) between database systems. Also, you won't have your head wrecked trying to parse arrays (in SQL - nightmare, code - barely better). – Vérace Feb 24 '16 at 19:44
  • 1
    What if I am using a nosql database? – Muhammad Raihan Muhaimin Feb 24 '16 at 20:46
2

You will want to use the Lookup Table approach. If absolutely necessary there are tools in most database implementations you can use to turn rows into an array like result for another application to use. But as far as Databases are concerned stick to the row method to maintain a nice clean relational model you can JOIN easily on.

  • 1
    Won't it save a SQL query with the array column approach? – Franklin Yu Sep 30 '16 at 21:41
  • 2
    By save do you mean use less queries? That's entirely dependent on how your writing your queries and for what purpose, but there is no reason a single well built query could not return results in any desired format from either example. However, SQL is not designed to handle array lists of data, trying to manipulate or JOIN on that sort of data circumvents the Relational model that most SQL implementations are based around. If there is no reason for that data to ever exist as anything besides a List, then sure it's a better. But if your actually relating data across multiple tables, then no. – Duffy Oct 3 '16 at 18:57

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.