CODEWITHSUNDEEP
ruby-on-railsarraysrubydatabaseactiverecord
Mikko Vedru
Mikko Vedru

Reputation: 353

Best database practice: using simple array or with key pair value?

I need to save a certain parameter in the database. Let's say the payment type for an online order.

models/order.rb

PAYMENT_TYPES = [ "Check", "Credit card", "Purchase order" ]
validates :pay_type, inclusion: PAYMENT_TYPES

views/orders/_form.rb

f.select :pay_type, Order::PAYMENT_TYPES, prompt: 'Select a payment method'

Question1: What are the advantages and disadvantages of using simple array PAYMENT_TYPES = [ "Check", "Credit card", "Purchase order" ] versus an array with key/value pair PAYMENT_TYPES = [ ["Check", 1], ["Credit card", 2], ["Purchase order", 3] ]?

Question2: Which one should I use if I know that there can be any kind of changes in the future (including changing of names and combining of categories)? Does it matter if actually matter whether database value is a number or a text-string? What's the practical difference?

Question3: Should I use string like above or should I use :symbols? If :symbols are better, then how can I make the original string available for view? Something like PAYMENT_TYPES = [ [:check, "Check"]]? Can I use :symbols with number value and still have string text available? I am very confused.

Upvotes: 0

Views: 90

Answers (2)

lurker
lurker

Reputation: 58324

If you have a table of purchase transactions and each record has a "purchase type" attribute (which is either "check," "credit card," or "purchase order," I probably would have a purchase_types table in the database rather than coding the table:

purchase_type_id (int)    purchase_type_name (string)
-----------------------------------------------------
1                         Cash
2                         Credit Card
3                         Purchase Order

This puts the data where the data belongs: in the database where it's easier to manage, not hard-coded in your source. And it's more easily modified. (You might want to add "Paypal" later... :)) It also gives you the best of both worlds: your table of purchases is more compact (no redundant string attribute entries since it uses the purchase_type_id) and the database is self-contained (the meaning of the id's is in the data, not in the code).

Then your selector for the purchase item would use this table to get the select box choices. It's a common Rails pattern.

In your new question about symbols, you can easily convert between symbol and string in Ruby, so it's redundant to have, for example, ["Check", :check]. But it's a moot point if you use a purchase_types table.

Upvotes: 2

sawa
sawa

Reputation: 168249

I don't know how you are going to use the numbers in the subarrays in the second option, but it looks quite clear that there is no point in using the second option.

(i) If you want to relate the items in the arrays to a unique number, you can use the array index in the first option, which makes the numbers in the second option redundant.

(ii) Also, looking up for an element in the array is easier if you have a flat array rather than a nested array.

(iii) Finally, for maintenance reasons, the first option is clearly superior; if you happen to add/remove/change the items from the array, you will be still ensured to have a unique consecutive numbering in the first option, but in the second option, you would have to renumber the whole thing.

Upvotes: 1

Related Questions