On Wed, 9 Jun 2010, Tim Romano wrote: > 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.
To generalize this, when designing a database schema it is best to look at the relationships involved. For example, the customer-order relationship is 1-to-many (each customer may have many separate orders). However, orders and items are a many-to-many relationship (each order can have many items, and each item can be on many orders). Usually, many-to-many relationships require an intermediate table that uses the primary keys from both tables as a composite primary key. And, as Tim wrote, separating the order header from the line item details makes both creation and maintenance much easier. Rich _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
