Page 1 of 1
mysql db schema with joins.
Posted: Wed Aug 17, 2005 1:08 pm
by dk4210
Hello Guys,
I have a website that is going to have several areas such as "contractors" , "erectors", "companies",
and others. I am going to have a quote system and more.. Can you give me some ideas of a db schema.
My DB is called sbcontractors and I have 1 table called "users" for my login and account levels. I need to do
this correctly now before it grows any larger. I have to think about joining tables too.. I hope I am clear enough.
Please help.
Thanks, Dan
Posted: Wed Aug 17, 2005 1:48 pm
by RobertGonzalez
Before you get too far into the design of your DB, give it some thought. Maybe even draw it out on paper. When the draft is done, build it and test it locally. As your development takes on different avenues modify your database to suit your development until it is a "release quality" app.
As far as the exact shema of the DB, you need to offer more information as to what tables you want to have and what the tables will do. Knowing that is the first step in developing your database.
Posted: Wed Aug 17, 2005 2:42 pm
by dk4210
I guess my problem is that I have so many areas that I am not sure how to lay the db out to conform to my site.
I have several differnt parts to my site. This is kinda how I have it laid out on paper..
Website -
http://www.steelbug.com
Code: Select all
Database - sbcontractors
Table - users
Sql - See below
[ Companies ] [ Contractors ] [ Erectors ] [ Concrete finishers ]
\ \ \ \
\ \ \ \
\ \ \ \
\ \ \ \
____________________________________________
[ LOGIN ]
____________________________________________
|
|
| User level 1,2,3,4,5
|
----------------------------------------------
Administration based on user
Code: Select all
CREATE TABLE users (
userid int(25) NOT NULL auto_increment,
first_name varchar(25) NOT NULL default '',
last_name varchar(25) NOT NULL default '',
business_name varchar(60) NOT NULL default '',
address varchar(100) NOT NULL default '',
city varchar(25) NOT NULL default '',
state varchar(22) NOT NULL default '',
zip varchar(5) NOT NULL default '',
phone varchar(11) NOT NULL default '',
email_address varchar(25) NOT NULL default '',
username varchar(25) NOT NULL default '',
password varchar(255) NOT NULL default '',
info text NOT NULL,
account varchar(5) NOT NULL default '',
user_level enum('0','1','2','3','4','5') NOT NULL default '0',
signup_date datetime NOT NULL default '0000-00-00 00:00:00',
last_login datetime NOT NULL default '0000-00-00 00:00:00',
activated enum('0','1') NOT NULL default '0',
out_of_state char(1) NOT NULL default '',
additional_state varchar(22) NOT NULL default '',
PRIMARY KEY (userid)
) TYPE=MyISAM COMMENT='Membership Information';
Posted: Wed Aug 17, 2005 2:43 pm
by dk4210
wowo it messed up my chart.. dang!
Posted: Wed Aug 17, 2005 2:48 pm
by feyd
you could have fixed it...

Posted: Wed Aug 17, 2005 4:03 pm
by RobertGonzalez
And what is this table going to be used for? Just validating logins, or for other things as well? In my opinion (and this is only an opinion) I like using int(11) for dates and translating the integer into a date using date functions in the code. It is easier for me to do that than to convert a date/time value from the DB into a timestamp then converting back (mostly for interval determination and such).
Give a little more information about your DB. Right now it appears that your DB will be used solely for validating user logins and maybe showing information in a directory listing of some sort.