A bit of help with a database

Naffa · 19 Apr 2008 at 18:06

One of my friends has turned to me for help with his database, and I haven't done anything with databases in months and have no clue.

Basically, he's making a database for a pitch and putt and wants to have a field that shows the amount of games each person has played. But he wants the field to gather the information from a table showing the amount of transactions. And he wants to do it for each customer.

Sorry, I forgot to add. He wants to add a field into the customer table saying how many times they've played, and he wants to use the data from the transaction table. He wants to do it via a means of displaying how many transactions have had each customer ID.

I don't think I could have explained this any worse if I'd tried.

Thanks for any help offered.

GarethDW · 19 Apr 2008 at 18:20

If I've understood this correctly, your friend wants to determine the number of visits each customer has made to his pitch 'n' putt course based on the number of transactions there are for that customer.

In my opinion calculating the number of visits based on transactions isn't the best way to do it because a) what happens if there are multiple players but only one person pays for everyone (e.g. a family goes and the Dad pays for them all) or b) someone pays for multiple games in advance, i.e. they know they're going to do 2 rounds so pay for 2 games up front, which would only show as a single transaction but they've played 2 games.

Naffa · 19 Apr 2008 at 18:40

Thanks for taking the time to post.

I just spoke to my friend, and he said that he wouldn't mind if only one person's total plays increased if they played with their family (or something). He said it would give him something to add to his evaluation at least.

GarethDW · 19 Apr 2008 at 20:52

I'll assume your friend needs to design a relational database and normalise it. Off the top of my head, I'd start with something like this (bearing in mind I've not really touched this since my university days last century

):

Code:

CUSTOMER
--------
CustID 
FirstName
LastName
Address1
Address2
Address3
Telephone
Email
DoB


TRANSACTIONS
------------
TransactionID 
CustID
ProductID
Date
Time

PRODUCTS
--------
ProductID
ProductName
ProductDescription
Price

That should do as a starter for ten.

Working through this... all customer details are stored in the Customer table. All the types of games/services on offer (single round, family visit, season ticket, etc...) available are stored in the Products table, and the Transactions table stores each time a customer pays for a game/service.

Each customer will have multiple transactions but each transaction will only relate to one customer. Each transaction could potentially relate to multiple products and of course each product relates to multiple transactions.

To calculate the number of times each customer has visited (bearing in mind the flaws I pointed out earlier) you could therefore run a query such as:

Code:

select CustID, COUNT(TransactionID) as "Number of Games Played" 
from TRANSACTIONS 
where ProductID = 1 or ProductID = 2 or ProductID = 3 
order by CustID ASC

That works in my head but I've not tested this so it may not give you what I think it does... but it'll give your friend something to work from until someone with more experience in this than me comes along and provides a better solution

LazyManc · 19 Apr 2008 at 21:19

GarethDW said:
I'll assume your friend needs to design a relational database and normalise it. Off the top of my head, I'd start with something like this (bearing in mind I've not really touched this since my university days last century ):

Code:

CUSTOMER -------- CustID FirstName LastName Address1 Address2 Address3 Telephone Email DoB TRANSACTIONS ------------ TransactionID CustID ProductID Date Time PRODUCTS -------- ProductID ProductName ProductDescription Price

That should do as a starter for ten.

Working through this... all customer details are stored in the Customer table. All the types of games/services on offer (single round, family visit, season ticket, etc...) available are stored in the Products table, and the Transactions table stores each time a customer pays for a game/service.

Each customer will have multiple transactions but each transaction will only relate to one customer. Each transaction could potentially relate to multiple products and of course each product relates to multiple transactions.

To calculate the number of times each customer has visited (bearing in mind the flaws I pointed out earlier) you could therefore run a query such as:

Code:

select CustID, COUNT(TransactionID) as "Number of Games Played" from TRANSACTIONS where ProductID = 1 or ProductID = 2 or ProductID = 3 order by CustID ASC

That works in my head but I've not tested this so it may not give you what I think it does... but it'll give your friend something to work from until someone with more experience in this than me comes along and provides a better solution

To do the transaction<->product many-many relationship you'd need an associative table (transaction_product) with the id for each related record.

Naffa · 19 Apr 2008 at 22:01

Wow, that's awfully kind of you to type all of that out. :cool:

Thanks a lot!

I'll forward it onto him straight away and get a response in. Again, thank you. You really didn't have to type all of that out.

GarethDW · 19 Apr 2008 at 22:59

LazyManc said:
To do the transaction<->product many-many relationship you'd need an associative table (transaction_product) with the id for each related record.

Yes, good point.