LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 08-23-2004, 08:14 PM   #1
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Rep: Reputation: 32
MySQL db table


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
 
Old 08-23-2004, 08:32 PM   #2
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
What are you going to do, if the person has multiple emails, vessels, vehicles, radios or postal-addresses?
 
Old 08-23-2004, 08:34 PM   #3
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Original Poster
Rep: Reputation: 32
what do you mean by that ?

tt
 
Old 08-23-2004, 09:00 PM   #4
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Original Poster
Rep: Reputation: 32
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
 
Old 08-23-2004, 09:00 PM   #5
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
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.
 
Old 08-23-2004, 09:05 PM   #6
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Original Poster
Rep: Reputation: 32
Would it help if i show you the form ?

test page

TT
 
Old 08-23-2004, 09:11 PM   #7
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
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.
 
Old 08-23-2004, 09:18 PM   #8
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Original Poster
Rep: Reputation: 32
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
 
Old 08-23-2004, 09:36 PM   #9
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Original Poster
Rep: Reputation: 32
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
 
Old 08-24-2004, 05:24 AM   #10
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
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.
 
Old 08-24-2004, 05:57 AM   #11
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 12.04 - Server 12.04.4 LTS
Posts: 935

Original Poster
Rep: Reputation: 32
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Table copying in MySQL prabhatsoni General 4 10-26-2004 05:43 PM
MySQL: Can't use '(' or ')' in table name Mikessu Linux - Software 0 08-03-2004 03:59 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM
deleted mysql table in mysql now cant do anything nakkaya Linux - Software 0 03-18-2003 06:03 PM


All times are GMT -5. The time now is 02:34 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration