change record that is part of composite key

[Gavx]

I am creating a database that tracks the selling of products amongst other things.
The user will enter in an order and may delay invoicing until the customer approves the quote - at which time the order is turned into an invoice.

In the transactions table the OrderID, ProductID and CustomerID constitute a composite key.
I want to be able to view the order and change it by either deleting or adding ordered items (obviously prior to invoicing) but because the ProductID is part of the composite key I cannot delete a line item.
Perhaps the solution is to remove ProductID from the index or is there a better way?
I don't really want to add another field that marks the item as Deleted - if you know what I mean

 

[MarkK]

I would give each table a unique autonumber primary key. That way every row is uniquely identified without any dependencies.

 

[Gavx]

How would this work?

What would the autonumber in the transactions table relate to?

 

[MarkK]

The autonumber in a table uniquely identifies each row in that table. Related child records then store that ID as their foreign key link to that table. So if I have a journal object against which I can make many posts, then I have . . .

tJournal
JournalID (Primary Key)
Date
etc...

tPost
PostID (PK)
JournalID (Foreign Key)
AccountID (FK)
Amount

So one journal entry is the parent to many related posts, and each of those posts is also the child of an account. This is a classic Many-To-Many relationship. One account can post to many transactions, one transaction can post to many accounts. Not shown is the tAccount table.

 

[Cronk]

Why do you want a composite index? Remove CustomerID and ProductID from the uniquq index. The OrderID should be the key field in tblOrders

 

[Gavx]

The OrderID should be the key field in tblOrders

In my tblOrders table each item in each order is listed. Therefore a variety of lines with one OrderID. Is this what you are suggesting?

Is my design faulty?

 

[Gavx]

Here is a picture of my relationships.

I believe I have what you describe in the form of the Guest table. However the first 3 fields in the guest table represent the composite index. Because individually none of them are unique in this table.

This all came about because I am building a form that will allow the functionality to amend an order prior to invoicing. But when I try to amend a line item I receive an error message disallowing the change because it will result in duplicate indexes.

 

[gemma-the-husky]

first, in the general solution you need 2 tables

order header (key - ordernumber, FK - customer. The customer does not need to be part of the PK)
order lines (key - ordernumber plus product - ie no customer in the key, or even in the record)

a single order table is appropriate only if there is only ever one item per order.

----
given, this second scenario, if you want to change the product on an order, then it is much easier.

irrespective, personally I would probably not change an existing line. I would tend to one these

either change the quantity to zero,
set a flag that says "deleted"
delete the unrequired line

and then add a new line

 

[jdraw]

Gavx,
There are links to free videos here. I recommend you watch them and see how the tables and relationships are developed and why.

Good luck.