how to block user for duplicate entry

Hi.
I have a cart table:

Code:

---

cart_id (PK)
item_id (FK)
item_unit_price
order_qty
order_price
order_of
order_instructions
order_date
token_id (NOT FK)

---

Another table Order_Master

Code:

---

or_id (PK)
or_token_id
or_of
or_date
user_id

---

And Order_Detail Table:

Code:

---

or_idd (PK)
or_id (FK)
item_id (FK)
item_unit_price
or_qty
or_price
or_instructions

---

This is a dummy project for my practice, learning web developments and mySQL. The customer comes to a restaurant and places order to the user/ cashier/ owner standing at a restaurant counter. Once the order is taken the record is stored in CART table till the completion of order. Once the order is ready to serve/ take away the user click the sales button which removes the CART record and shift the entire record of that order to the Order_Master and Order_Detail table and Sale_Info Table.
Every customer comes to the restaurant whether to dined in or to take away his/ her order; He/ she is given a token number. When the order is completed, the customer returns the token to the user/ cashier/ owner and pays the bill and takes the cash receipt. Token number information is also stored in CART table and then same info moves to the Order_Master, Order_Detail and Sale Table.


I want to block the user to enter (mistakenly/ deliberately) the same token in current-date for new customer. This is a very basic question but the problem is that:
Neither I can make a check from Orders table no on CART table.
I can't match new Token Number with the token numbers lying in CART because upon every completion of order, CART table is cleared. So no Record in CART.
I even can't match new token with token number lying in Orders table because if last token ID in Order table is 10 and token 11, 12, 13 are in CART. Which means that system indicates that the last token ID is 10 but in actual its 13 because of CART.

Question:
How to block the user for duplicate entry?

Thank you.

Hmmm.....going by your cart-table, the user can only add one item to the cart, since cart_id is a Primary Key.
Have you thought about separating the items from the cart?

table cart
cart_ID (PK)
order_date
order_instructions
user_ID (FK to user)
token_ID

table cart-details
cart_detail_ID (PK)
cart_ID (FK to cart)
item_ID (FK to item)
other detail fields

that way, in cart you could declare cart_ID and token_ID as unique together(!)

When order is finished, and cart emptied out, the same applies to the table order: or_ID and token_ID together as unique

I'll address this part:

Quote:

---

I can't match new Token Number with the token numbers lying in CART because upon every completion of order, CART table is cleared. So no Record in CART.
I even can't match new token with token number lying in Orders table because if last token ID in Order table is 10 and token 11, 12, 13 are in CART. Which means that system indicates that the last token ID is 10 but in actual its 13 because of CART.

---

First Cart shouldn't be emptied... it should only be emptied of the token which is the current user... other users could have stuff in the cart with their own token, as indicated by your last statement.
Secondly, what you would need to do is check BOTH tables... you seem to think you'd check one or the other... that's not the case... you check both tables.
Some thing like this:

Code:

---

Select 1 from cart where token_id = 'your token here'
union all
Select 1 from Order_Master where or_token_id = 'your token here'

---

If that returns one or more rows, you know the token is in use.

That said, hopefully you plan to only call this one, before the first time you add to the cart... because once that user adds to the cart, that token will exist in the query.

Might I also suggest a couple more changes... IT sounds like you plan to use an incremental number model for your token... don't... I'd go with a GUID instead. Most DBMSs have a built-in GUID generation function that will produce a unique value each time you call it - they're partially time based. You can generate it and be pretty much guaranteed that it doesn't exist - I'd still run the check, but it'll be faster than going, how about 5? 6? 7? 9? ..... 300? 301? and so on...

-tg

The same problem

The same solution.