Mysql help: Read

[Trium918]

I know that this is a Mysql issue, but I posted in the

Mysql forum and only like 1 person is viewing that forum.

There isn't that much help over there like it is here in PHP Help

forum.

 

What are the advantages and disadvantages with the stucture

of this database I am attempting to build?

 

create database members_super;

use members_super;

create table members_info(members_id int unsigned not null auto_increment primary key,
-> user_name varchar(25) not null, first_name varchar(25) not null, 
-> last_name varchar(25) not null, gender varchar( not null,
-> contact_number int(10) not null, email_address varchar(100) not null  );

create table members_address(address_id int unsigned not null auto_increment primary key,
-> members_id int unsigned not null, street_address varchar(50) not null, 
-> city varchar(25) not null, state varchar(10) not null, postal_code int(5) not null  );

[only one]

there are no advantages and disadvantages, it depends on how you want it to work

 

if you want people to be able to use special characters such as. <>*!@#$%^&*() etc.. varchar will let this happen...

[Trium918]

there are no advantages and disadvantages, it depends on how you want it to work

 

if you want people to be able to use special characters such as. <>*!@#$%^&*() etc.. varchar will let this happen...

 

Is that a bad thing knowin that specific characters/scripts is used

in for SQL injection?

[Psycho]

I think it looks pretty good.

 

However, I would use a tinyint for gender with just 0 and 1. Also, the lengths for the fields look OK, but there are always those exceptions that won't fit.

 

Postal code may be a problem though. You have it set up as a number. Unless you have a very good reason for doing so, I suggest you set it up as a varchar. Because if you use a numeric field you will lose leading 0's (i.e. 02985 will be changed to 2985). Yes, you could always pad the zip code, but why create more work for yourself. Plus, you may want to allow the zip +4 area code which would require 10 characters.

 

You also have phone number as an int which shouldn't be a problem with leading 0's since phone numbers don't start with 0, but I would still go with a varchar. Otherwise you will need to strip out any separators the user may enter (i.e. 123-456-7890).

 

As for SQL injection you need to account for that anyway. The database schema will not prevent SQL injection.

[Trium918]

The is what I have know, but is there anything

else I could do to make this database better?

Like the Grant command because the user

will have to select,insert,update,and delect or is there

another way around granting user these permissions?

 

create database members_super;

use members_super;

create table members_info(members_id int unsigned not null auto_increment primary key,
-> user_name varchar(25) not null, first_name varchar(25) not null, 
-> last_name varchar(25) not null, gender tinyint( not null,
-> contact_number varchar(10) not null, email_address varchar(100) not null  );

create table members_address(address_id int unsigned not null auto_increment primary key,
-> members_id int unsigned not null, street_address varchar(50) not null, 
-> city varchar(25) not null, state varchar(10) not null, postal_code varchar(5) not null  );

[Trium918]

The is what I have know, but is there anything

else I could do to make this database better?

Like the Grant command because the user

will have to select,insert,update,and delect or is there

another way around granting user these permissions?

 

create database members_super;

use members_super;

create table members_info(members_id int unsigned not null auto_increment primary key,
-> user_name varchar(25) not null, first_name varchar(25) not null, 
-> last_name varchar(25) not null, gender tinyint( not null,
-> contact_number varchar(10) not null, email_address varchar(100) not null  );

create table members_address(address_id int unsigned not null auto_increment primary key,
-> members_id int unsigned not null, street_address varchar(50) not null, 
-> city varchar(25) not null, state varchar(10) not null, postal_code varchar(5) not null  );

 

Anymore Ideas?

[Barand]

There is a simple rule - don't double post.

 

You're right, it is a MySQL issue so I'm closing this one.