mysql db schema with joins.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dk4210
Forum Newbie
Posts: 9
Joined: Sat Jul 30, 2005 12:06 pm

mysql db schema with joins.

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
dk4210
Forum Newbie
Posts: 9
Joined: Sat Jul 30, 2005 12:06 pm

Post 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';
dk4210
Forum Newbie
Posts: 9
Joined: Sat Jul 30, 2005 12:06 pm

Post by dk4210 »

wowo it messed up my chart.. dang!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you could have fixed it... :|
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply