Create table

[ShaolinF]

Hey Guys,

 

I want to create a table with the following columns. Can you please give me your recommendations:

 

ID (Must be unique, starting with 00001, id should increment by 1)

Name

Gender (Male or female)

Contact Number

Email Address

Time Of Registration

IP

Paid (Yes/No)

[fenway]

ID (Must be unique, starting with 00001, id should increment by 1)

-PK, auto_increment, BIGINT UNSIGNED, deal with the zerofill on the output only, no in the DB

 

Name

-varchar, 255

 

Gender (Male or female)

-ENUM

 

Contact Number

-varchar,255 -- unless you can be sure you're only storing north american numbers, in which case CHAR(10) is sufficient

 

Email Address

-varchar,255

 

Time Of Registration

-store date & time, in the DATETIME() field, use NOW() on INSERT

 

IP

-UNSIGNED INT, store with INET_ATON() -- don't store the dotted octet

 

Paid (Yes/No)

-ENUM... but it seems like it's missing lots of info (when, how much, for what, etc.)

 

Could use some normalization when there are more fields.

 

At the very least, index name -- but that really should be first and last separately, right?

 

[ShaolinF]

Thanks. Well Im not too good with MYSQL so you'll have to excuse me if I ask some daft questions. 

 

The paid is only for a set of tickets, a simple paypal buynow type of button.

 

So how exactly would I create the query, something like this:

 

CREATE userinfo (

    Name varchar(255),

    Gender ENUM,

    ContactNo varchar(255),

    EmailAdd varchar(255),

    TimeOfReg ( ??? ),

    IP UNSIGNED INT (not sure entirely),

    Paid ENUM );

 

What is the purpose of an index name ?

[ShaolinF]

Also, could you give me a sample of data I could use in the fields (just to be sure).

[fenway]

Thanks. Well Im not too good with MYSQL so you'll have to excuse me if I ask some daft questions. 

 

The paid is only for a set of tickets, a simple paypal buynow type of button.

 

So how exactly would I create the query, something like this:

 

CREATE userinfo (

     Name varchar(255),

     Gender ENUM,

     ContactNo varchar(255),

     EmailAdd varchar(255),

     TimeOfReg ( ??? ),

     IP UNSIGNED INT (not sure entirely),

     Paid ENUM );

 

What is the purpose of an index name ?

Even so, when did they buy the tickets? How many? Confirmation #? etc.

 

ENUM requires a list of possible values; check the refman for syntax.

[ShaolinF]

Well the thing is, they fill in a small form and press next where they are redirected to paypal. They choose how many tickets they want on the paypal system. Their is little control over what data I can extract from paypal. So the timeOfReg can be the purchase date, or maybe I could add another timeOfPurchase field ? They will recieve a confirmation email from me once they have submitted and also a receipt from paypal.

 

Is it ok if you could go through the create statement and correct any errors in it for me please ? Im quite new to SQL and still have a long way to go. 

 

And thanks for your suggestions, some thought provoking questions.

[fenway]

I'm sure there is a lot of info you can ask paypal to "send back" to you... ENUM ('Male','Female') and ENUM( 'Yes','No' ) are examples of correct syntax.

[ShaolinF]

Lol, I was checking out paypal's site and yes they can send back alot of data, which now means I must postpone the DB creation untill I can get a better view of all the table structure.

[fenway]

I would consider dumping all the paypul info into another table entirely.

[ShaolinF]

Why would you say that ?

[fenway]

Because it's not user info... it's purchase info.

[ShaolinF]

Thanks. So would I create a relationship with the two tables though the same ID ? Correct ?

 

Here is an example set of tables:

 

table 1

UserID,

name,

age,

gender,

 

table 2

UserID,

ProductName,

purchase ID,

quantity,

unit price,

total.

 

So if I wanted to display UserId, name, productname, quantity, total - how would I do that ?

[fenway]

You'd simply join the tables.

[ShaolinF]

Thanks.

Ok, earlier on I mentioned that I wanted my ID numbers to be complete random numbers rather than incremented. Well, I have plans of putting a system into place where current registered users enter their ID into a textbox and it will display their reg. details. The problem with incremented IDs is someone could probably 'guess' the number (or atleast increases its chance with a incremented ID).

The reason I only want them to beable to enter IDs is to make things quicker, its not a large system and its only to purchase tickets (for university events).

 

Whats your take on this ?

[fenway]

Then you need usernames & passwords.