MySQL db table

tommytomato · 08-23-2004, 08:14 PM

Hi all

I've made my 1st db using MySQL-Front

I'm unsure if its right, can any one check it out please.

Is there a better program to use to build a database

here is my table with in the db i made last night
seem to conntect ok using MySQL on FC2

Code:

CREATE TABLE `members` (
  `Id` int(6) unsigned NOT NULL auto_increment,
  `access_level` varchar(20) binary NOT NULL default '',
  `username` varchar(10) binary NOT NULL default '',
  `password` varchar(8) binary NOT NULL default '',
  `cookie` varchar(32) binary NOT NULL default '',
  `session` varchar(32) binary NOT NULL default '',
  `ip` varchar(15) binary NOT NULL default '',
  `name` varchar(20) binary NOT NULL default '',
  `surname` varchar(20) binary NOT NULL default '',
  `phone` varchar(8) binary NOT NULL default '',
  `mobile` varchar(10) binary NOT NULL default '',
  `address` varchar(50) binary NOT NULL default '',
  `boat_photo` varchar(50) binary NOT NULL default '',
  `member_photo` varchar(50) binary NOT NULL default '',
  `postcode` varchar(4) binary NOT NULL default '',
  `street` varchar(100) binary NOT NULL default '',
  `suburb` varchar(20) binary NOT NULL default '',
  `email` varchar(100) binary NOT NULL default '',
  `signed` varchar(50) binary NOT NULL default '',
  `date_signed` varchar(10) binary NOT NULL default '',
  `date_joined` varchar(10) binary NOT NULL default '',
  `member_number` varchar(5) binary NOT NULL default '',
  `training_one` varchar(100) binary NOT NULL default '',
  `training_two` varchar(100) binary NOT NULL default '',
  `vessel_rego_no` varchar(10) binary NOT NULL default '',
  `hin` varchar(30) binary NOT NULL default '',
  `vessel_length` varchar(10) binary NOT NULL default '',
  `make` varchar(10) binary NOT NULL default '',
  `type` varchar(20) binary NOT NULL default '',
  `material` varchar(20) binary NOT NULL default '',
  `vessel_name` varchar(50) binary NOT NULL default '',
  `colour_hull` varchar(20) binary NOT NULL default '',
  `deck` varchar(20) binary NOT NULL default '',
  `trim` varchar(20) binary NOT NULL default '',
  `canopy` varchar(20) binary NOT NULL default '',
  `engine_make` varchar(20) binary NOT NULL default '',
  `h_p` varchar(20) binary NOT NULL default '',
  `aux` varchar(50) binary NOT NULL default '',
  `fuel_type` varchar(20) binary NOT NULL default '',
  `engine_ser_no` varchar(50) binary NOT NULL default '',
  `aux_engine_ser_no` varchar(50) binary NOT NULL default '',
  `usual_ramp` varchar(50) binary NOT NULL default '',
  `alternate_ramp` varchar(50) binary NOT NULL default '',
  `radio_type` varchar(50) binary NOT NULL default '',
  `radio_model` varchar(20) binary NOT NULL default '',
  `radio_serial_no` varchar(50) binary NOT NULL default '',
  `vehicle_rego_no` varchar(20) binary NOT NULL default '',
  `vehicle_make` varchar(20) binary NOT NULL default '',
  `vehicle_body_style` varchar(20) binary NOT NULL default '',
  `vehicle_colour` varchar(20) binary NOT NULL default '',
  `trailer_rego_no` varchar(20) binary NOT NULL default '',
  `trailer_axles` varchar(10) binary NOT NULL default '',
  `trailer_lights_numberplate` varchar(10) binary NOT NULL default '',
  `message` varchar(100) binary NOT NULL default '',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `UNIQUE KEY` (`username`)
) TYPE=MyISAM;

Thanks tt

ToniT · 08-23-2004, 08:32 PM

What are you going to do, if the person has multiple emails, vessels, vehicles, radios or postal-addresses?

tommytomato · 08-23-2004, 08:34 PM

what do you mean by that ?

tt

tommytomato · 08-23-2004, 09:00 PM

you mean add more fields or make the varchar bigger as in text ?

I know what you mean, i have seen locally that a few people have more than one vessel etc etc

I'm trying to under stand how it all works

TT

ToniT · 08-23-2004, 09:00 PM

Well, I have no slightest idea of for what you are going to use that table, but
just guessing some uses from the names of the columns.

I'm not sure what you are asking, but if you are asking if the table you have writen here makes semantically sense, I'm trying to enlighten this aspect.

So I rephrase, if every row represents an user, which has an unique id and username, and that email is represented as one field of the row, then user can't have multiple emails according to this structure. Similary it can't have multiple vessels, vehicles, radios, or addresses. And in reverse, every user must have atleast one vessel, vehicle, radio, etc.

I'm just checking here if that is what you want.

tommytomato · 08-23-2004, 09:05 PM

Would it help if i show you the form ?

test page

TT

ToniT · 08-23-2004, 09:11 PM

Well, there is probably no problem with one vessel/radio/etc.. situation.

What I was looking for was somewhat more theoretical aspect on how relational databases work in general and thus next question is how them can be used effectively to model your situation.

tommytomato · 08-23-2004, 09:18 PM

You lost me abit there

looking at the link now

I did create different fields in the db before but a mate said not too, to create all fields needed in one table

instead of having different tables

members_details as in one table
vessel_details as another table
radio_details as another table
towing_vehicle_details as another table

and so on

tt

tommytomato · 08-23-2004, 09:36 PM

thats a good link makes sence, alittle hard to understand, i'll read it over and over again

this is what i meant before

Code:

# MySQL-Front 3.1  (Build 8.4)


# Host: localhost    Database: rvsrgdb
# ------------------------------------------------------
# Server version 4.0.18-nt

#
# Table structure for table members_details
#

CREATE TABLE `members_details` (
  `Id` int(6) unsigned NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `surname` varchar(20) default NULL,
  PRIMARY KEY  (`Id`)
) TYPE=MyISAM;

#
# Dumping data for table members_details
#


#
# Table structure for table vessel_details
#

CREATE TABLE `vessel_details` (
  `Id` int(6) unsigned NOT NULL auto_increment,
  `hin` varchar(50) default NULL,
  `rego` varchar(10) default NULL,
  PRIMARY KEY  (`Id`)
) TYPE=MyISAM;

#
# Dumping data for table vessel_details
#

tt

ToniT · 08-24-2004, 05:24 AM

The idea:
Members {
UID Primary-key,
name,
surname
}

Vessels {
ID Primary-key,
Owner References(Members(UID)),
Hin,
rego
}

That vessels.owner is a foreign key referring to members.uid, so if you want a member with two vessels, all you have to do is to have two lines in vessels table referring to the same owner.

tommytomato · 08-24-2004, 05:57 AM

So your saying add another field to the table , i get the idea kind of.

same for the radio_details and car_details and so on

TT