7fiEQ5rB

1Date
2Value1 
3Value2 
4Value3
5Value4 
6Etc. 
7     
81/1/10,1,2,3,4
91/2/10,5,6,7,8
101/3/10,9,10,11,12 
11     
12Value1
13Value2
14Value3
15Value4 
16     
17Description1, Description2, Description3, Description4 
18     
191/1/10,1,Description1,2,Description2,3,Description3,4,Description4
201/2/10,5,Description1,6,Description2,7,Description3,8,Description4 
21     
22DROP TABLE IF EXISTS table1 /* Assigned Values per date (the mapping table)*/;
23DROP TABLE If EXISTS table2 /* Values */;
24CREATE TABLE IF NOT EXISTS table2 (valueid INTEGER PRIMARY KEY,value_description TEXT);
25CREATE TABLE IF NOT EXISTS table1 (date TEXT, valueid_reference INTEGER REFERENCES table2(valueid),value INTEGER, UNIQUE(date, valueid_reference));
26INSERT INTO table2 VALUES (1,'Value1 Description'),(2,'Value3 Description'),(3,'Value3 Description'),(4,'Value4 Description');
27INSERT INTO table1 VALUES
28    ('1/1/10',1,1),('1/1/10',2,2),('1/1/10',3,3),('1/1/10',4,4),
29    ('1/2/10',1,5),('1/2/10',2,6),('1/2/10',3,7),('1/2/10',4,8)
30;
31SELECT date, group_concat(value||','||value_description) AS all_values_and_descriptions FROM table1 JOIN table2 ON valueid_reference = valueid GROUP BY date; 
32     
33select *
34from table1
35join table2 
36     
37select date, value1, 'Description1', value2, 'Description2', value3, 'Description3', value4, 'Description4'
38from table1; 
39     
40user
41----
42id bigint primary key
43name text not null
44
45items
46-----
47id bigint primary key
48name text not null
49
50orders
51------
52id bigint primary key
53user_id bigint references users(id)
54created_at datetime
55
56order_items
57-----------
58order_id bigint references orders(id)
59item_id bigint references items(id) 
60     
61select i.name
62from order_items oi
63join items i on i.id = oi.item_id
64where oi.order_id = ?