DB / App Best Practice

Rails
1

Hey Everyone.

Just wondering what I should do in regard to how much data (what type
of data) to store in the db.

If I have a table of cars:

–cars–
id - PK
type
color

In regards to the color is it best to just store a value for the color
as an int, the string of the color (ie. “blue”) or have a second table
to represent the colors ie:

–cars–
id - PK
type
color_id - FK

–colors–
id - PK
value

I can see some ups and downs to both. Not having the colors table
means the colors/values need to be maintained in the app itself. Where
as having the colors in the table can make it easier to expand etc.

I’ve run into this kind of problem on a few occasions and am wondering
what way to go about it.

On the same idea but may have a completely different answer as this is
a bit more specific. When it comes to users. Is it a good idea to
store ALL users (including admin) in the same user table? Separated by
an account_type (ie. reg_user, admin_user). If this is the case should
I make an account_type table to store account types?

Thanks for the input,
brianp

2

For admins and users, I would keep them both in the same table with a
role field to differentiate them. It really depends on your security
requirement and if admins and users login using the same
authentication mechanism. A role table for lookup is required.

For colors, MYSQL allows an ENUM type for a field. But it isn’t
directly supported by Rails.

might help if you are using MYSQL and want to modify Rails to support
it. For your particular case, you need to take a call based on data
volume and the attributes you want to segregate. Maybe a cars table
and a car_attributes table with something more than just a color in
there.