Setting up DB tables in Rails

Question

I am trying to set up the following infrastructure using Ruby/Rails. To explain my problem here is an analogy of the tables I am trying to set up: Say I have 3 main items I want to model/represent as tables: Parts, Manufacturers and Cars. So Parts table contains all the unique component parts each manufactured by a manufacturer. Manufacturers table contains all the unique manufacturers who make the parts. Cars table contains all the unique cars. A car is made up of parts and a manufacturer makes many parts. What is the most efficient structure of tables I can use to represent this information? I want to be able to query what are all the parts in a given make of Car and who made the part. I am new to DBs, new to Rails and so would like to understand what my options are in achieving this smoothly.

Answer 1

Part belong_to :manufacturer and has_many :cars

Car has_many :parts

Manufacturer' 'has_many :parts

Read this for detailed explanation of how to set up things.

Answer 2

If I understand your question correctly:

1. A manufacturer makes many different types of parts, but each type of part is made only by one manufacturer.

2. A car contains many types of parts, and a type of part can be used in many types of cars.

3. For a given type of car, you want to know which manufacturer(s) made the parts for that car.

In that case, you could set up the following associations between the Manufacturer, Part, and Car models:

1. A "one to many" (1:M) association between manufacturers and parts. (Add has_many :parts to manufacturer.rb and belongs_to :manufacturer to part.rb.)

2. A "many to many" (M:M) association between parts and cars. (Add has_and_belongs_to_many :cars to part.rb and has_and_belongs_to_many :parts to car.rb.)

3. A "has_many :through" association, from cars through parts to manufacturers. (Add has_many :manufacturers, through: :parts to car.rb.)

Then, use migrations to create a table for each model.

For a 1:M association between manufacturers and parts, there should be a column in the parts table called manufacturer_id. Each part's manufacturer_id (or "foreign key") is an integer that equals the id (or "primary key") of a particular manufacturer in the manufacturers table. In other words, that part "belongs to" that manufacturer. Many other parts can belong to that manufacturer as well.

For an M:M association between parts and cars, you'll need to create an additional table (with a model) that has columns called parts_id and cars_id. This is known as a "join table." If part X is included in car Y, the join table will have a row where parts_id equals X and cars_id equals Y. In this way, a part can have many cars and vice versa.

The association between cars and manufacturers is indirect. A car can have many parts, and each of those parts belongs to a manufacturer. Thus, a car can have many manufacturers.

Here's the best introduction to relational databases that I've found.

One more thing: For an M:M association, instead of a double-sided has_and_belongs_to_many you can use a double-sided has_many :through. Either way, the association requires three tables (parts, cars, and the table that joins them together). I won't get into explaining all of the differences or taking a side in the debate between HMT and HABTM. I think the bottom line is, "has_and_belongs_to_many" is simpler, but "has_many :through" will give your app more flexibility, especially later on.

--Edits in response to comment--

Each table automatically contains an integer column called id. That's called the primary key. So there's no need to add part_id to the parts table, manufacturer_id to the manufacturers table, etc.

But because there's a 1:M association between manufacturers and parts, the parts table will need a foreign key, manufacturer_id. If a part belongs to a manufacturer, then the foreign key of that part's row in the parts table must equal the primary key of that manufacturer's row in the manufacturers table.

Note that many foreign keys can point to the same primary key, but not vice versa.

M:M associations are a little different. Neither the cars table nor the parts table needs a foreign key. Rather, there must be a third table that includes foreign-key columns named car_id and part_id.

The parts table doesn't need a manufacturer_name column. The manufacturer name will be in a column called name in the manufacturers table. You can get it by following the association between a part and its manufacturer. For example, if @my_part is the Ruby object that represents a certain part, then @my_part.manufacturer will give you that part's manufacturer, and @my_part.manufacturer.name will give you that part's manufacturer's name.

Displaying information is a whole other topic in itself! Suffice it to say, you can create an object representing all the parts of a car whose id is X, as follows: @parts_for_this_car = Part.where(car_id: X)

Then, the object @parts_for_this_car will be an array (specifically, an array of Part objects). Create this object in a controller action (such as def show in controllers/car_controller.rb), and you can use it in the corresponding view file (such as views/cars/show.html.erb).