design advice or suggestion

[Chummly66]

I am in the beginning stages of remaking a database app that I use. The one I use now works but stoes all data in one table. granted that isnt the best way but I knew just enough to be dangerous <G>

I track repairs I do for customers. And right now, I redid my tables and broke it down to 3 tables. 1) Customer Data, 2) Customer Repairs, and 3) Customer Loaners.

What might happen on a typical day is a customer comes in and needs repair work done. I search for thier phone number. if I find it, my first form lists the customer info and thier past repairs. If not found, I enter their data and start a new repair.

If thier repairs take long or have to be shipped out, they will be issued a loaner.

Currently, I have relations set up as follows:
- from tblCustomer to tblEquipment. CustomerID (pk) in tblCustomer is tied to CustomerID (non-PK) in tblEquipment.
- from tblequipment to tblRepairs. txtESNDEC(non-pk) in tblEquipment is tied to txtESNDec (non-pk) in tblRepairs.
- from tblrepairs to tblLoaners. txtLoanerNumber (non-PK) in tblrepairs is tied to LoanerID (pk) in tblLoaners.
- from tblCustomer to tblLoaners. CustomerID (pk) in tblCustomers is tied to CustomerID (non-PK) in tblLoaners.

My goal is to tie everything together under the customer. The key is a customer may have 1 or more repairs, but each repair will have only one loaner, if at all.

Does the above look ok or should I make changes? I am no new to using relationships, that from everything I have read, its either I am doing too little, or making it too complicated.

My other thought would be to consolidate my tblEquipment and tblrepairs and just keep it in one table, tblrepairs. then my relationship would be:
- from tblCustomer to tblRepairs. customerID (PK) in tblCustomer is tied to customerID (non-pk) in tblRepairs.
- from tblrepairs to tblLoaners. txtLoanerNumber (non-PK) in tblrepairs is tied to LoanerID (pk) in tblLoaners.
- from tblCustomer to tblLoaners. CustomerID (pk) in tblCustomers is tied to CustomerID (non-PK) in tblLoaners.

Any insight or suggestion would be exptremely appreciated!

Richard

 

[sdk]

Hi

These are very difficult questions to answer, and you are best posotioned to answer them. Overcomplicating a dbase design is just as big a problem as oversimplifying it. Dbase design is also a wicked problem: multiple correct answers, no obvious solutions.

From your description I can't discern the reason for the tblEquipment. Is it to store info on your customer's equipment? If so, how much can you control the accuracy of this info? Why are you collecting it? Do your customers know you are doing this?

Applying the KISS principle, I would be strongly inclined to the second solution. I would also look for rules that support your current operations and that allow for variations. Your rule that a loan is only made against a repair is a constraint that could cause grief if you change your mind.

Personally, I would relate the repairs and the loans to the customer (in part, because in reality that is what you are doing: modelling close to 'realty' is always a good move), and would not be concerned about relating loans and repairs. I would ensure the interface supported all info needs for a customer is as shallow (ie minimum clicks away) as possible.

Lastly, be sure to handle a change in phone numbers well, as your whole system seems strongly dependant on them.

Cheers

 

[lespaul]

have you read the fundamentals document linked below?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design