Reputation: 611
I'm busy with a rails 4/postgres project that is structured in such a way that requires dozens of fields for a specific object, this being company. Each company would include fields for eg name, location, header_bg, logo, contact name, contact cell etc so the fields very well could grow in the future. What is the best way to structure this DB.
The way I see it I have 3 options:
1) company table with all these fields in the same table
pros: simple, data all in one place and easy to query
cons: table could get very messy and requires manually editing table structure every time a new field is required
~~~~~~
2) company table with an additional company_options table a foreign key company_id. This table will also hold multiple fields
pros and cons are same as above except this will be more neater structure. Crucial data such as company name will go in company table, other data like location, theme, header_bg etc would go in company_options
~~~~~~
3) company table and a company_meta table. This will follow the Wordpress structure of the post_meta table. So it would have the fields: company_id, meta_key, meta_value. each field would go in its own row
pros: flexible, able to dynamically add options/fields without editing table structure
cons: not a simplistic approach with same data in same columns. would need to custom build this functionality for inserting, updating, validation displaying of data. Unless Rails has such a gem which I can't find?
~~~~~~
Any suggestions or additional options would be appreciated. Are there any rules of thumb for determining the kind of structure needed? I started out with the 1st option out of a matter of simplicity but then started to build the 3rd and the amount of code just didn't seem right for what seems like such a common need.
Thanks!
Upvotes: 0
Views: 181
Reputation: 177
Plan your database schema for the future:
Will a company be able to have several phones?
For repeating information, use a new table:
rails g model phone number:string type:string
rails g migration add_company_id_to_phones company:references
This way you can add several phones and navigate your database to get each phone.
Will your field only have one of a kind?
For example, you might only need one name
field for each company
. If this is the case, add it directly to the companies
table:
rails g migration add_fields_to_companies name:string
Is your data completely optional? Is it unstructured?
Add a JSONB field to your table, assuming you are using Postgresql:
rails g migration add_details_to_companies details:jsonb
With JSONB data type you can use unstructured data like this:
Company.create({
name: "AwesomeCompanyCorp",
details: {
"main phone": "000-0000-0000",
"secondary phone": "000-0000-0000",
"support email": "support email",
"location": "Main Avenue 165",
"latitude": "22.330213123",
"longitude": "60.000012312"
}
})
With JSONB you are able to have unstructured data, but be able to query the field like this:
Company.where("details->>'main phone' = ?", "000-0000-0000")
With this on mind, choose the best approach that fits your case.
Upvotes: 1