Databases
StackOverflow2013
Postsdbo
POWhat is a better way to set up a database for an app? Normalized vs real world
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is a better way to set up a database for an app? Normalized vs real world
    text
    copied!<p>Consider, please this setup for a database backed application. ( in my case DB is MySQL and app is in Ruby ( Rails 3), but I don't think it matters for this question)</p> <p>Let's say I have an app for a warehouse.</p> <p>I have multiple items that would have categories and statuses.</p> <p>For example table that has parts would have a few statuses such as: in stock, discontinued, backordered and multiple categories, such as: it hardware, automotive, medical, etc.</p> <p>Also I have other tables that need statuses and categories, such as Vendor: approved, out of business, new Order: Open, processes, shipped, canceled. </p> <p>Etc.</p> <p>Here is the question:</p> <p>I think if I wanted to properly normalize my db - I would have a table called categories, categories_types, statuses, statuses_types.</p> <p>Then I would store all categories in that table, and any category that is of a certain type, such all categories of parts, would have a foreign key to category_type - parts, and so on. Same for types.</p> <p>This is the normalized way.</p> <p>However I often see that people create separate tables for specific categories, for example, there would be a table called part_categories, vendor_categories, order_statuses, part_status. This is a less normalized db, but I guess when you are dealing with a lot of tables, it might be clearer.</p> <p>Which of this approaches is a better one? What are the cons &amp; pros in your experience? I usually go with the first setup, but I see the second one so often that I'm beginning to doubt my approach.</p> <p>Thank you.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload