Database normalization question

Database normalization question

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 by Joe Philllips for Database normalization question

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 by Randy for Database normalization question

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 by KM. for Database normalization question

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 by Brian Gideon for Database normalization question

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 by Philip Kelley for Database normalization question

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.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72