Database normalization question

Question

Hi all,

I am trying to make a fashion boutique site. In this site each product (t-shirt,jeans etc) belongs to a collection. Each collection has looks (t-shirt,jean,accessories). A product can belong to one collection and to multiple looks. How should I design the database?

Answer 1

Put a 'collection' field in your Product table. It will be a foreign key to your Collection table (which might just be an ID and a Name.)

Can a look have many products? If so, use a many-to-many relationship (intermediary table) to model that.

Edit: You will start with 3 main tables: Products, Collections, Looks

You will then need joining tables: Look-Products, Collection-Looks

Answer 2

I would start with tables similar to these

product
----------
product_id
description

collection
----------
collection_id
name

look
----------
look_id
name

collection_look
---------------
collection_id
look_id

product_collection_look
---------------
product_id (fk1)
collection_id (fk2)
look_id (fk2)

Answer 3

try something like this:

Products
ProductID    int auto increment PK
ProductName  string
CollectionID FK to Collections
...more columns if necessary...

Collections
CollectionID   int auto increment PK
CollectionName string
...more columns if necessary...

Looks
LookID        int auto increment PK
LookName      string
...more columns if necessary...

CollectionLooks
CollectionID  composite PK,FK to Collections
LookID        composite PK, Fk to Looks

ProductLooks
ProductID     composite PK, FK to Products
LookID        composite PK, Fk to Looks

Answer 4

Here is one idea. I used SQL Server syntax for illustration. It was not completely clear whether a collection could have one look or many looks. I assumed many looks below, but it would really easy to change that. Again, this is just one idea using relationship tables, but there other equally valid possibilities.

create table Product
(
  ProductId int not null primary key,
  Name varchar(128) not null unique
)

create table Look
(
  LookId int not null primary key,
  Name varchar(128) not null unique
)

create table Collection
(
  CollectionId not null primary key,
  Name varchar(128) not null unique
  LookId int not null references Look (LookId)
)

create table CollectionLook
(
  SurrogateId int not null primary key,
  CollectionId int not null references Collection (CollectionId),
  LookId int not null references Look (LookId),
  constraint CollectionLookConstraint unique (CollectionId, LookId)  
  -- Change the above constraint if a collection can only have one look.
)

create table ProductCollection
(
  SurrogateId int not null primary key,
  ProductId int not null references Product (ProductId),
  CollectionId int references Collection (CollectionId),
  constraint ProductConstraint unqiue (ProductId)
)

create table ProductLook
(
  SurrogateId int not null primary key,
  ProductId int not null references Product (ProductId),
  LookId int not null references Look (LookId),
  constraint ProductLookConstraint unique (ProductId, LookId)
)

Answer 5

Given that:

• A Collection contains Products, and Products "have" Looks.
• A Collection also "has" Looks

and making no assumptions, I see two big questions.

Can a Collection have (be assigned) Looks, but contain no Products that have been assigned those Looks?

If true (a Collection can have Looks but no Products with those Looks), then the "multiple tables" models everyone's posted so far would work: tables Collection, Product [FK to Collection], Looks, CollectionLooks, ProductLooks.

If false (a collection cannot have Looks unless at least one of it's Products has those Looks), then you'd have to toss the CollectionLooks table, giving you a simple hierarchical structure. Whenever you needed to determine a Collection's Looks, you'd have to "query through" from Collection to Products to ProductLooks. If performance were somehow a problem, you could denormalize and set up the CollectionLooks table, but keeping it accurate would be awkward at best.

Can a Collection contain Products with a given Look, but not itself be assigned that Look?

If true (Collection A contains Product P with Look Z, but Collection A itself does not have Look Z), then you'd have to use the multiple tables model, to keep what has which Looks straight.

If false (If Collection A contains Product P with Look Z, then Collection A must have Look Z), then the hierarchical model is again most approrpiate.