Hi

I am making a CMS system for an e-shop.
The goal is client would be able to manage their own catalogue including categories and products.
I don't have any problem creating categories that has many products.
But I cant think of how to allow user configure their own product's attributes.
For example: a fashion store has color and size for their product, while others don't.

How to make the structure without duplicating the item record? all i can think of is having several records of the items with same name, price, summary, description but with different color and size (color and size column is customized, so may not be there also).

I hope everyone can understand what I'm trying to explain.
Please help me. Many thanks in advance.

Normalisation..
This is what you need.

As I understand it now, you are cramming all the attributes of an article in the same table than the article, like this:

id   name   size   color       
---------------------------
1   shirt      l       red       
2   shirt      l       blue

What you need is to normalize this table, to his simple expression, and split the informations in separate tables:

table users:
  userId integer auto_increment
  userName varchar(50)
  email varchar(50)
  ...

table article:
  articleId integer

table articleName:
  articleFk integer references article(articleId)  -- link to the article
  userfk integer refrences users(userId)           -- link to the user
  language char(2) default 'EN'
  articleName varchar(100)

table articleColors:
  articleFk integer references article(articleId)
  userfk integer refrences users(userId)
  color varchar(20)

table articleDetails:
  articleFk integer references article(articleId)
  user fk integer refrences users(userId)
  summary text
  description text

That way, what is generic is present 1 time (article)
but the attributes of those articles can be matched on a "per user" base

Wow! What a swift reply!

So, are you saying that I have to make one new table per every attribute I made?
e.g. 1 table for color, 1 table for size, etc..?
does each table has their own primary key?

Then how to make the transaction/sales table?
By the time, user pick a red shirt size L, i need to link all the tables up there right?
If they don't have primary key.... I don't know how to connect them...

So, are you saying that I have to make one new table per every attribute I made?
e.g. 1 table for color, 1 table for size, etc..?
does each table has their own primary key?

That would be the peak of normalisation. and yes each table has a PK or unique id for the row.

If they don't have primary key.... I don't know how to connect them...

Don't you ever even think about having a table without a primary key !
Seriously, the whole goal of a rdbms (relational data base management system) is to link tables between themselves.
You need a key to do that.

What I gave you up there was "pseudo structure".
An outline of the basis of the tables, but not the complete definitions of them.
You have to extrapolate from there.

Then how to make the transaction/sales table?

It's up to you.
In the enterprise I work for, the sales system is basically based on 2 tables.
An header table, linking to a user, a cashier, the terminal, and a transactionLines table with every articles that are included in the transaction.

table transactions:
  idsTransactionId integer
  intCustFk integer references customers(idsCustmoerId)
  intPosFk integer references terms(idsTermId)
  dtmStart datetime
  dtmEnd datetime

transactionLines:
  intTransFk integer references transactions(idsTransactionId)
  intArtNr integer references articles(idsArtId)

And a bunch of satellites table around the transaction lines that describe thew price, if there was a reduction, and if the price was partially paid by a given mean, for how much (IE: a voucher is given but doesn't cover the whole amount, and the rest have been completed by a credit card)

Wow! Thanks a lot for your explanation.
Since it shakes my brain a lot, let me fully explain the requirement of the system.

I attached a JPEG of the ERD sketch.

The point here:
Categories could be main categories and sub categories.
A main category can have many subcategories.
Products can only be assigned to 1 subcategory.

Additional attributes are assigned to main categories.
Therefore, the subcategories and products that fall under a main category must inherits all the attributes that assigned to that main category.

Sales table must somehow linked to products, with their additional attributes attached. Like for example:
Yoke buy 3 shirt A size S: 1 red, 2 blue.
Please see the image for explanation.

I have no problem with fixed table, but since this is a cms system, admins must be able to add/edit/delete their additional attributes to a main category.

The image is all I can think about so far... but I believe I made mistakes somewhere....
I will go on and read about normalization further.

Please please please bear with me. I have all this fog inside my head, dunno how to solve this. Thank you very very much in advance.

I somehow understand what is tripy trying to explain about normalisation, here is 1NF.
I believe I have already applied that in my ERD.
But doesn't solved my problem.
The problem here is I cannot make 1 table for 1 attribute, since attributes for each main categories may not be the same.
For example, guy's shirt have color & size, but bags only have size.
And lingerie (e.g bra) may have color, size, and cup.

Normally, I would recommand you to have 1 "attributes" table per type of article.
This to allow you to have defined properties for a type of article.

But I understand it can be tedious, and if you are cautious about it's use, why not have 1 table attributes.
I have used a structure like this:

table attributes:
  intArtId integer
  attribName varchar(100)
  strVal varchar(100)
  boolVal boolean
  intVal integer
  numVal numeric
  dtmVal datetime

This allow me to attach X attributes to 1 article, and to give it a name and a value.
I kept distinctive types fields to leverage the db optimizations on certain datatypes.
Given that you can only have 1 value for a attribute, the select would be done using coalesce():

select coalesce(strVal, boolVal, intVal, numVal, dtmVal) as value from attributes where artID=x

yeah, i think i'll try this one first....
thanks alot tripy.
i think i can see the sun now. =)

Basically I'am building a really basic CMS. How would I go about create a tree like structure?

Say there are 4 main sections:

Home
About
Services
Contacts

All of which are just database records in a table, how would I amend my database so that i could add pages underneath each of those categories.

Any help would be appreciated - no time waster please that are just looking for points, it gets boring!

Copy and paste muppet

http://answers.yahoo.com/question/in...8051308AAOJ0wp

didn't even have the sense to delete the last sentence.