Help with lotto program at concept stage

Hi there,

Im currently devising a lotto program and I'm at the stage where I am designing my database on paper firsts while I discover what is and isn't possible and now I need the help of some one experienced to help answer a couple of questions if they could.


Here is how I envision the program working (roughly)

Form to take details:

Name Email Tel

Select amount of tickets

Show Price Total

Randomly generate non repetetive ticket strings per ticket

Go To PayPal

(Maybe use php sessions to pass data to and from)

Store info in temp DB (based on a suggestion on these forums in another post)

Pass session back

Success = move info from temp db to main db > Clean out temp DB

Failure = Clean Temp DB But keep PHP session


Now, what I need to know is

1. In a table field, could I store say 10 randomly generated strings of say 10 numbers or letters

2. Can I take say a paypal receipt or transaction number back from using paypal so that I can store it with the ticket and customer info?

The problem I am having at the minute is the most efficient way to store the customer info, the tickets and some form of paypal number so that I can view all that info in the back end when I design it.
This way, I can view all transactions and customers.

I have designed my first table which will contain

fname lname tel total ticketnum paypalreceipt date/time


They are not the column names but show what they represent.
Im wondering if I should make one table for customer info and one for tickets and paypal receipt numbers and link them maybe?
The biggest problem is how to store all the ticket numbers if someone buys more than one ticket.

Any suggestions would be good here.

Im not looking for someone to do this for me, I just need a few pointers please.

Some who have spoken to me know Im a noob, but I really want to do this myself

Thanks in advance!!!!!

Ok, I've posted this question in a couple of other forums besides here and so far no one has answered.
I'll asume that know one knows how to design & build a DB then.

Thanks anyway!

Patience is a virtue the forums have been quite busy the last couple of days.

When you're starting to design a database, you want to keep everything as seperated as possible. You want to avoid having multiple types of data in the same table. So in your example above, you want to have customer table/ticket table/paypal tables etc.

Give each of them a unique column identifier and cross reference in each..

The customer would be one of the highest in the heirachy and the ticket column could have a reference to the customer id so that multiple tickets can point to the same customer. The paypal table receipts can have a reference to both ticket number and customer number.

Basically, a cross referenced column is called a foreign key and identifies a unique row in another column. Don't worry about how many tables you have, just be sure that they can be linked together with foreign keys.,

Hi Rakuli,

sorry if I seem to have come across as funny, I wasn't meaning to be, I woke up this morning full of beans raring to go but found I hadn't gotten an answer anywhere lol.

Ok, just so that I am 100% sure I get this, my table(s) would look something like this:

Table 1 - Users

| CustomerID | Fname | Lname | Email | Telephone |
-----------------------------------------------------------------
| 123456 | John | Smith | blah | 123456789|


Table 2 - Tickets

| CustomerID | TicketNumberString |
---------------------------------------------
| 123456 | abc-123-def-456 |
| 123456 | ghi-789-jkl-123 |


So customer ID would be the foreign key that links all the info to the one person?
Where customerID would be a randomly generated number or combination of letters and numbers unique to that individual.
Any entry into the database being stored in relation to that user would be stored alongside the CustomerID (foreign key) so that it is recoverable?

Am I understanding this correctly?

If so, would I also need to set a primary key for each table?

Sorry for the noobish questions but I want to be sure I understand everything fully before I begin.
I absorb info better when it's shown visually... I'm just wiered that way lol

Thanks again!!!

Ok, based upon the above assumption I have created the following tables

Table 1 - members

Code: Select all

CREATE TABLE members (
CustID int unsigned not null auto_increment,
Fname varchar(50),
Lname varchar(50),
Uname varchar(50),
Pword char(30),
Email varchar(90),
Chron DATETIME not null,
PRIMARY KEY(CustID, Uname));

Table 2 - Tickets

Code: Select all

CREATE TABLE tickets (
UserID int unsigned not null,
TicketNumbers varchar(60),
PurchaseTime DATETIME not null,
PRIMARY KEY (TicketNumbers),
FOREIGN KEY (UserID) references members(CustID));

Table 3 - paypal

Code: Select all

CREATE TABLE paypal (
Cid int unsigned not null,
PPreceipt varchar(60),
PayTime DATETIME not null,
PRIMARY KEY (PPreceipt),
FOREIGN KEY (Cid) references members(CustID));

I am assuming I have done this correctly:

I have created a column in each table to hold the CustID which will be associated with the data contained within that row.
In each table I have labled the CustID diferently.

Table 1 = CustID
Table 2 = UserID
Table 3 = Cid

So that no two tables contain the same named column.

Okay, so it's looking pretty good.

I assume you are using mysql because you are using auto_increment on the customer table.

There's no reason to have the foreign keys named differently in each table. It actually confuses things if you do.

You may also want to have a foreign key to the receipts table in the tickets table too as each a customer may pay for more than one ticket at a time.

Hi again Rakuli,

Yeah, I realised about the keys with diferent names last night.
The way I see it, you have a primary key in each table.
EG Table 1 primary key = CustID
In table 2 TicketString = Primary Key
Also in Table 2 we have a column also called CustID which is the foreign key from table 2 to table 1.

I am keeping a blog here as I progress and you can read the changes I have made here:
http://www.webforumz.com/blogs/wease...-update-1-116/

I think Im starting to understand it lol.

Every table needs a primary key. In the second table we need a column that matches the primary key in the first table, this is our foreign key (link) but we also have a new primary key to link to table 3.

It kinda chains one table to another using a common reference between the two table rows.



You say you asume I am using MySQL because of auto_increment, I'm sorry but I dont understand?
You mean there is an alternative to MySQL?

I know from reading last night there is some debate between auto_increment ID's and GUID's, which one is better.
Could you explain further please..

Thanks Rakuli, all help is greatly appreciated!!!!