Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm making a POS and currently kind of stuck on the order proccess due to the product attributes.

As of now the DB looks something like this:

tbl_products

tbl_attributes (attr id, name, extra price)

tbl_sales ( where i store the time, total price etc)

tbl_salelines ( where i store what items have being order for a certain sale).

Problem is storing the attributes for an item order. There isn't a fix amount of attributes one item can have. It could go from 1 to 10-15 therefor i don't think making some columns in the tbl_salelines with attr_1 attr_2 etc etc would solve this.

How should i approach this?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Assuming that the attributes are for records in the tbl_salelines.

If the relationship is such that one salesline can have many attributes, then add a column for salesline_id in the tbl_attributes table.

If the relationship is such that one salesline can have many attributes AND one attribute can have many saleslines, then add an intersect table with attribute_id and salesline_id, where both of these id's are primary keys in their own table and are foreign keys in the intersect table. It may look like this...

select * from tbl_salesline_i_attribute;

SALESLINE_ID  ATTRIBUTE_ID
------------  ------------
800           22
800           89
555           45
764           45


select * from tbl_salesline;

SALESLINE_ID  COLUMN
------------  ------------
800           foo
555           bar
764           foobar


select * from tbl_attribute;

ATTRIBUTE_ID  NAME               EXTRA_PRICE
------------  ------------       -------------
22            blue               3
89            glow-in-thedark   3
45            extra large        10

In the example above, salesline 800 has two attributes on it (blue and glow-in-the-dark). Likewise, attribute 45 is on two saleslines.

share|improve this answer
    
I ended up doing another table where i store the relations w, i was just hoping i could somehow avoid the extra table + extra queries. –  Teris L Mar 21 at 8:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.