Placing a unique composite index on (customer, article) in the Orders table prevents recurring purchases of the same article by the same customer. Acme might buy a widget in June and then place another order for a widget in September, but the order would be rejected as a duplicate.
Typically, Orders are divided into OrderHeader and OrderDetail tables: OrderHeader id integer primary key orderdate customerid OrderDetail id orderid references OrderHeader(id) articleid references article(id) quantity int And you could then place a unique composite index on (orderid, articleid) in OrderDetail if you wanted to prevent the same article from appearing on more than one line-item of the order. Regards Tim Romano Swarthmore PA Regards Tim Romano . On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters <oliver....@web.de> wrote: > Hello, > > despite it's just a question about construction I hope somebody is willing > to > push me into the right direction if necessary. > > my simplified case > ------------------ > I've the 3 tables customer, article and order > > my thoughts about the table customer: > the customernumber can be from 3 different sources with possible > overlappings > (i.e. I can get 36666 from source A and 36666 from source B) so I adopt the > field customerorigin to make a difference > For simplicity I created a field id that is taking the part of the Primary > Key > and just declared "UNIQUE(customernumber,customerorigin)" > > > the SQL-Code > ------------ > CREATE TABLE customer( > id INTEGER PRIMARY KEY AUTOINCREMENT, > customernumber INTEGER, > customerorigin INTEGER, > name TEXT, > UNIQUE(customernumber,customerorigin) > ); > > CREATE TABLE article( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ); > > CREATE TABLE order( > id INTEGER PRIMARY KEY AUTOINCREMENT, > id_customer INTEGER, > id_article INTEGER, > UNIQUE(id_customer,id_article), > FOREIGN KEY(id_customer) REFERENCES customer(id), > FOREIGN KEY(id_article) REFERENCES article(id) > ); > > > simple question > --------------- > Is this a correct way or do I make a mistake? > > greetings > Oliver > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
