Jump to content

The ultimate community for Ruby on Rails developers.


Photo

Struggling with a model because of a lot of data


  • Please log in to reply
7 replies to this topic

#1 alessandroprioni

alessandroprioni

    Passenger

  • Members
  • 4 posts

Posted 04 September 2013 - 01:53 PM

I'm building an app to write wine tasting notes, and I have to translate this tasting framework (only the first page) into a model.

It's a lot of a data and I'm not sure about how to proceed. I tried to sketch a possible solution in this spreadsheet.

What would you suggest to do? Should I create only one model (Wine) with a column for each wine characteristic?

Thanks!

P.S. I'm learning web development, sorry if my question sounds trivial.



#2 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 04 September 2013 - 02:06 PM

Any particular reason why you had to split the models? Why not just include everything in one Wine model? 

 

What you can do though is create models for the parameter values that will serve as lookup tables for the Wine model. 

 

Color    (list all your colors)

Appearance (pale, med, deep)

Intensity  (list all your intensity, low, med-, med, med+, high

Texture

Quality

DrinkReadiness

Price (inexpensive, mid, high, premium) 

Flavor (fruits, flowery, etc...) 

 

In the Wine database, you list the color_id, price_id, etc...  


  • alessandroprioni likes this

#3 alessandroprioni

alessandroprioni

    Passenger

  • Members
  • 4 posts

Posted 05 September 2013 - 09:21 AM

Thank you!

There's no particular reason: I was told that creating a table with a lot a columns was a bad practice, trusted it and tried to sketch a solution to avoid it :)

 

Do you think that creating only one model (Wine) and storing all the data inside it would work fine anyway?

There are two things about it I'am worried about though:
  1. some properties have the same name (i.e. both appearance category and nose category have an intensity property). I think a good way to deal with this would be naming the columns as follow :category_property (so, the previous example would be :appearance_intensity :nose_intensity)
  2. if the database grows, I guess I'll be better to "normalise" it eventually. Will it be fairly easy to move all the existing data to other tables? 


#4 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 05 September 2013 - 11:50 AM

I was told that creating a table with a lot a columns was a bad practice, trusted it and tried to sketch a solution to avoid it

 

 


What is "lots" ?  I think this should be looked at on a case by case basis. 

 

You don't want to end up with "lots" of skinny tables (few columns on each table) and then having to JOIN all of them even for simple queries, when a single table would have sufficed and simplified things. 

 

 

Do you think that creating only one model (Wine) and storing all the data inside it would work fine anyway?

 

 

I don't see anything wrong with a table that has lots of columns if you need to keep track of a lot of different properties/attributes for it.   

 

 

 

some properties have the same name (i.e. both appearance category and nose category have an intensity property). I think a good way to deal with this would be naming the columns as follow :category_property (so, the previous example would be :appearance_intensity :nose_intensity)

 

That's fine too. 

 

 

if the database grows, I guess I'll be better to "normalise" it eventually. Will it be fairly easy to move all the existing data to other tables? 

 

Not sure what you mean here.  Grow as in what? More new fields added in the future or more records?   

 

It's better to design it right from the start because once it's in production, it may be harder to change things.  You may have a lot more programs (written by others) accessing the same database and you might end up breaking some of these existing programs if you do dramatic changes on the underlying database schema. If it's just your program using the database, then you have more leeway. 



#5 alessandroprioni

alessandroprioni

    Passenger

  • Members
  • 4 posts

Posted 05 September 2013 - 02:47 PM

 

Not sure what you mean here.  Grow as in what? More new fields added in the future or more records?  

 

I meant more records. Since there are a limited number of choices for each property, I would have a table with a lot of repeated data for each column (i.e. the column :appearance_intensity has just three options). Isn't it a bad practice?

 

On the other hand, if I go with this solution:

 

What you can do though is create models for the parameter values that will serve as lookup tables for the Wine model. 

 

Color    (list all your colors)

Appearance (pale, med, deep)

Intensity  (list all your intensity, low, med-, med, med+, high

Texture

Quality

DrinkReadiness

Price (inexpensive, mid, high, premium) 

Flavor (fruits, flowery, etc...) 

 

I'll have ~20 models associated with the wine one. Is it fine, too? I guess that with your solution retrieving data from the database is more efficient: Wine.find_by(colour_id: 3) is better than Wine.find_by(colour: "red"), isn't it? Also, I could add indexes more easily.

 

What would you do if you were me?

 

I'm really confused..



#6 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 05 September 2013 - 03:22 PM

If you think the description won't change, you can hardcode it and store it in your Wine database. 

 

BUT

 

I think it's bad practice and not flexible... what if down the road, you decide to change the descriptive text to something else? Now, you'll have to go back to your Wine database and do a Find and Replace.... instead of just changing one record in your lookup table. 

Say, instead of calling it "inexpensive", down the road you decided to call it "cheap", or "best value". 

 

You'd just have to make your decision on what you think is a good medium if you think 20 lookup tables is too much.  For example, if quality will be always "low, medium, high", then maybe you don't need a lookup table for it.  Just store the actual text "low", "medium", and "high" in the wine database and just provide those options in your form <select> statements. (Of course, if down the road, you decided you need a "premium" and "super luxury" quality, you'd have to modify your program and <select> statements, making source code changes, instead of just adding those 2 new options in your lookup table.) 

 

Just find a happy balance you think you can live with, knowing the pros and cons of each approach. 



#7 alessandroprioni

alessandroprioni

    Passenger

  • Members
  • 4 posts

Posted 06 September 2013 - 06:00 PM

Hi Dispatcher,

 

I've received a lot of different feedback in these days, all different from each other :)

Anyway, yesterday I talked with a guy who works as a database administrator in a Italian big company and he suggested me to create a different model for each property in order to get a more flexible db - as you did in your first comment. Here is the result.

 

Would like to hear your opinions.

 

Thanks for your help!



#8 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 06 September 2013 - 08:39 PM

Would like to hear your opinions.

 

Thats the same thing I said... see my post #2.   :)

 

What you can do though is create models for the parameter values that will serve as lookup tables for the Wine model. 

...

...

 

In the Wine database, you list the color_id, price_id, etc...  






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users