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

Question

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.

What is the reason for using the second option, with the key/value pairs? What do the numbers mean, and where would they be used? — dwenzel
– dwenzel, Commented Dec 17, 2015 at 17:14
Symbols and strings are easily converted one to the other, so there's no point at all in, [:check, "Check"]. — lurker
– lurker, Commented Dec 17, 2015 at 17:20
Before learning Rails I went through the DB course. And if I remember correctly, the "correct approach" there was to have a table with number values (0 means "not known", 1 means "X", 2 means "Y"). In general I am very confused and don't have any clear reason. I am simply afraid to make a architectual-design mistake. — Mikko Vedru
– Mikko Vedru, Commented Dec 17, 2015 at 17:24
My original questions weren't about the specific payment-type, but a general enquiry about the best practices. Possible implementations can be anything: user types, specific permissions for each user type, status of fights, the way the fight ended (arm bar, TKO, ezekiel choke), status of the article, etc. — Mikko Vedru
– Mikko Vedru, Commented Dec 17, 2015 at 17:29

lurker · Accepted Answer · 2015-12-17 17:31:55Z

2

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.

edited Dec 17, 2015 at 17:31

answered Dec 17, 2015 at 17:26

lurker

58.5k9 gold badges74 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mikko Vedru Over a year ago

"selector for the purchase item would use this table to get the select box choices. It's a common Rails pattern." - how could this be done?

lurker Over a year ago

@MikkoVedru have a look at this stackoverflow question and answer. You might find it helpful.

sawa · Accepted Answer · 2015-12-17 17:18:52Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related