Dear VB Pros,
Please I want to solicit your assistance on a comprehensive database design for the following explanation:
I have a customer order form comprising of:
1. the following controls will get information from the customer that will be stored in the customer table.
[[B]CustomerName[/B]], [[B]CustomerAddress[/B]], [[B]City[/B]], [[B]State[/B]], [[B]DateofPurchase[/B]]
2. [[B]QtyDemandedTextBox[/B]]: this will be used to get no of qty of messages demanded by a customer. This QtyDemanded will depend on QtyAvailable (i.e, if [[B]QtyDemandedTextBox[/B]] > [[B]QtyAvailableTextBox[/B]] Then, MessageMe.Show "error")
3. [[B]MessagesListBox[/B]] - used to display list of messages in the library, and the customer can select. The selected Item can also be stored in the database table for transactions purpose.
4. [[B]SpeakerTextBox[/B]] - this will display the name of speaker for a particular message selected from the [[B]MessagesListBox[/B]].
4. [[B]DateofPublishedTextBox[/B]] - this will display the date in which a particular selected messaged was published.
5. [[B]QtyAvailableTextBox[/B]] - this will display the quantity of produced messages available in the database.
6. [[B]CostOfItemTextBox[/B]]- this will display the cost price for a particular selected message
7. PaymentType: - there are 3 RadioButtons signifying type of payment which are:
a. [[B]CashRadioButton[/B]] - whether the customer wants to pay in cash
b. [[B]ChequeRadioButton[/B]] - whether the customer wants to pay in cheque
c. [[B]GiftCertRadioButton[/B]] - whether the customer has a gift certificate
8a. [[B]ChequeNoTextBox[/B]] - set to enable if the customer selected [[B]ChequeRadioButton[/B]] as a payment type else set to disable.
8b. DateOfChequeDue - this variable will be used to collect concatenated date strings from date controls set to enable if the customer selects [[B]ChequeRadioButton[/B]] as a payment type. But the control is split into 3 different ComboBoxes - ([[B]DayComboBox[/B]], [[B]MonthComboBox[/B]] and [[B]YearComboBox[/B]]). So, [[B]DateOfChequeDue[/B]] = [[B]DayComboBox[/B]] & "/" & [[B]MonthComboBox[/B]] & "/" & [[B]YearComboBox[/B]]
9a. [[B]TotalCostTextBox[/B]] - this Readonly control will calculate and display the total cost of selected messages based on the No of [[B]QtyDemandedTextBox[/B]].
9b. [[B]AmountDepositedTextBox[/B]] - usually, come customers can purchase a $20 item and give $100 note. In this scenario, they demand a ReturnChangeValue of $80. So, this control will be used to enter the deposited amount by a customer. Pls see 9c.
9c. [[B]CustomerBalanceButton[/B]] - this button's onClick event will calculate the difference btw the TotalCost and [[B]DepositedAmount[/B]] and will display the balance on a Readonly [[B]BalanceTextbox[/B]]...pls see 9d.
9d. BalanceTextBox - this control will display the balance from the calculated difference above.
10. [[B]CustomerHasBalanceCheckBox[B]] - this control will be used to record information on whether the customer has collected balance or not.
I was contemplating on using 2 RadionButtons here for a Yes/No scenario. But however, you can put me through.
11. [[B]NameOfAttendingClientTextBox[/B]] - this control will display the Name of the Customer Service Attendant on the Form and possibly his/her ID will be recorded to a table - I don't know which table it is.
I want these tables to be designed with all possible relationships.
Note: I have presumed the following tables:
Customer, Messages, Transaction, Payment, Cheque, ClientLogin, Speakers, TransactionDetails etc
Please kindly help me.
Thank you.
Eyo, Honesty