CODEWITHSUNDEEP
mysqldatabasedatabase-design
cwiggo
cwiggo

Reputation: 2609

Database Design - Catalogue - Range - Product

Can anyone suggest a database design for the following:

A user can make a catalogue Within a catalogue a user can make a range - i.e. a range of products Within a range a user can add multiple products Within a range a user can add multiple ranges -> range->range->range all with products in them.

I currently have in my database -

catalogue_range with - id, name, description and catalogue_product with - id, range_id, name, description

can anyone see what I'm trying to produce?

My aim is to be able to make multiple catalogue ranges within a catalogue range and add multiple products to each of these catalogue ranges.

Here is my current SQL:

`catalogue_range` (
      `id` char(40) NOT NULL,
      `profile_id` char(40) NOT NULL,
      `type` enum('pdf','db') DEFAULT NULL,
      `status` enum('new','draft','live') NOT NULL,
      `name` varchar(64) NOT NULL,
      `description` varchar(1000) NOT NULL,
      `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `profile_id` (`profile_id`)
    )

`catalogue_product` (
  `id` char(40) NOT NULL,
  `catalogue_id` char(40) NOT NULL,
  `order` smallint(5) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catalogue_id` (`catalogue_id`)
)

Thanks in advance.

Upvotes: 2

Views: 442

Answers (2)

Joel Brown
Joel Brown

Reputation: 14408

Assuming that a product can only exist in one catalogue at a time, your design is almost alright as it is. What you are missing is a recursive foreign key on catalogue_range. Add something like the following to your catalogue_range table definition:

`parent_range_id` char(40) NULL,
FOREIGN KEY (`parent_range_id`) REFERENCES catalogue_range(`id`)

The top level range(s) for any given user will have a NULL parent_range_id, others will refer to the containing range. Note that hierarchies aren't necessarily easy to work with in SQL. You may also want to look into techniques for making hierarchical data more SQL-friendly, such as nested sets.

Upvotes: 1

Sebas
Sebas

Reputation: 21542

  1. catalogue(catalogue id, your private attributes)
  2. product(product id, #catalogue id, your private attributes)
  3. range(range id, #range id parent, your private attributes)
  4. product range(#product id, #range id)

You will need stored procedures/applicative algorithms to compile:

  • the list of product of a range (to calculate recursive sqls mysql doesn't offer analytic functions as oracle does)
  • the list of ranges of a catalogue/range

Hope it helps. S.

Upvotes: 1

Related Questions