1

Let's say I have a site that sells laptop computers of different brands and specs. Specs include things like CPU power, memory, hard disk space, and even such things as insurance, service, and accessories.

They should all be searchable via full search text and faceting/filtering, like this: http://www.pricegrabber.com/computers/laptop/p-13/

The database is postgresql.

My plan, stupid or not, is to have a table for each spec itself. So I would create a table for cpus where I have all the different options for CPUs, like "Intel 4.33 Ghz Quad Core" or whatever, and repeat the process for things like hard_disks, memories, video_cards, and so on.

In the end, thus, the main table would look something like this:

class CreateLaptops < ActiveRecord::Migration
  def change
    create_table :laptops do |t|
      t.references :brands_id
      t.references :models_id
      t.references :cpus_id
      t.references :memories_id
      t.references :video_cards_id
      t.references :displays_id
      t.references :batteries_id
      t.references :accessories_id
      t.references :insurances_id
      # and the list goes on ...
      t.timestamps null: false
    end
  end
end

Is this stupid design? I'm aware this could be based on opinion, but I would love any pointers, like if I'm objectively doings things totally wrong. I sort of plan to even go deeper, i.e. referencing something which in turn references something. cpus could be split up into intel_cpus and amd_cpus, for example, but now I feel I've gone too far.

The most important consideration is making it all easily searchable from the outside, then make it easily maintainable and organized, and at the bottom of the list comes performance, because there won't be many items in the database at all.

Looking forward to hear your thoughts!

  • Why don't you use something like an "Article-Number" as primary key ? With such a number you would determine every product uniquely. – SklogW Feb 01 '15 at 18:51
  • @SklogW Well that's automatically assigned, I presume. Not really the crux of the matter unless I misunderstand you. –  Feb 01 '15 at 19:32
  • Have look [here](http://stackoverflow.com/questions/21010251/inventory-management-with-stock-options/21113170#21113170) – Mohsen Heydari Feb 02 '15 at 08:58

1 Answers1

0

It sounds to me like you want to nest resources many layers deep, something like this:

class Laptops < ActiveRecord::Base
  has_many :cpus
end
class CPUs < ActiveRecord::Base
  has_many :intel
  has_many :AMD
end

As I assume by "making it searchable from the outside" you mean you want to make it a good, RESTful API, think about what the route you have to specify for the first AMD of the first CPU of the first laptop:

example.com/laptops/1/cpus/1/amd/1

Or imagine internally when you are trying to link to certain a certain path:

link_to laptops_cpus_amd_path

Not very readable. And that is only three layers deep.

You said you want to make all this stuff searchable by full text search. How would this database design help that at all? Postgresql can already do full text search out of the box, i.e, if in your laptops table you had a value "Intel 4.33 Ghz Quad Core", PG can find that. Abstracting the CPU to its own model won't help that at all, and will only make your API worse.

In fact, there is an excellent Railscast on the subject of PG full text search:

http://railscasts.com/episodes/343-full-text-search-in-postgresql

trosborn
  • 1,658
  • 16
  • 21
  • 1
    Faceting is rarely done with paths like that, it'd never make much sense. Full-text search on laptop specs doesn't make much sense, because you'd need to type everything out-faceting is different. OP specifically states a need for *both*, and breaking down components seems reasonable for faceting. – Dave Newton Feb 01 '15 at 21:33
  • Thanks for your answer. There will be no need to actually link to the different CPUs or whatever, only the laptops themselves. I know that postgres can do full text search out of the box, that's precisely what I am using (well, with the pg_search gem). And as stated, full search sure can find that, but the "Intel 4.33 Ghz Quad Core" will be meaningless then in itself, i.e. hold no *actual* value, which makes it impossible to come up when someone wants to list CPUs between 4 and 4.5 Ghz, for example. –  Feb 01 '15 at 21:48
  • @DaveNewton May I inquire as to your opinion on my approach? –  Feb 01 '15 at 21:49
  • Oh oops, I should have read your post more carefully. Faceting is outside of my purview. – trosborn Feb 01 '15 at 22:41