Database structure for product attributes on orders 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?
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-the-dark   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.