I'm curious whether my concept of database design is correct. Here is my case:
Usage for a car lot.
Sales, Accounts, CashSales
The concept is that redundancy is eliminated by keeping Time_Sold in Sales, and all operations based on Sales (Sales for the month, total sales etc.) can be executed with a simple query.
The problem is that, depending on interface design (i.e. PHP site), an Account and a CashSale can have the same Sale_ID... Which is incorrect!
My question is this: is this proper design? It doesn't seem that way.
Also, what follows is my other choice for a solution, and the same question still applies.
Sale_ID, Type ENUM('Account','CashSale'), Related_ID
The concept is that each Sale is either an Account or CashSale and queries would be based around this fact. The only problem is determining which table each record is in. In order to do this, you must check whether the Type is an Account or CashSale. My question in this regard is, is this correct to discern related tables based on data in a record?
1) Is the design of Case #1 correct?
2) Is the design of Case #2 correct?
3) Is it correct to determine which table to used based on data in a record?