Solved

DB Logic

Posted on 2004-08-20
6
237 Views
Last Modified: 2012-05-05
Im playing around with creating a client management script for my small computer service business.   This will not be an enormus corporate DB that has to be optimized for speed etc...  Just me and my partner using it.  
So Here is my planned layout.  Tell me if I'm on the right track:
here is the table structure:
results table
this table holds all the main client info

`id` mediumint(9) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `email` varchar(100) default NULL,
  `aim` varchar(100) default NULL,
  `msn` varchar(100) default NULL,
  `yahoo` varchar(100) default NULL,
  `irc` varchar(100) default NULL,
  `ircalias` varchar(100) default NULL,
  `sitename` varchar(100) default NULL,
  `paymentmethod` varchar(100) default NULL,
  `totalprice` varchar(100) default NULL,
  `amountre` varchar(100) default NULL,
  `deadline` varchar(100) default NULL,
  `startdate` varchar(100) default NULL,
  `completion` varchar(100) default NULL,
  `refered` varchar(100) default NULL,
  `comments` longtext,
  `packagename` varchar(100) default NULL,
  `payment` varchar(100) default NULL,
  `total` varchar(100) default NULL,
  `clientpaying` varchar(100) default NULL,
  `typeof` varchar(100) default NULL,
  `amount` varchar(100) default NULL,
  `referedby` varchar(100) default NULL,
  `comment` longtext,
  `address` text NOT NULL,
  `address2` text NOT NULL,
  `city` text NOT NULL,
  `state` text NOT NULL,
  `zip` text NOT NULL,


email table
`id` mediumint(9) NOT NULL auto_increment,
`emailaddress` varchar(50) default NULL,
`clientid` mediumint(9) default NULL,
this table is for email addys.  I don't want to have a set number of email address so I figured Id setup another table for those.  I plan on using the results.id number and attaching it to each email addy so the client could have as many as i want.


password table
this is for client passwords.  Same as above, you never know how many different passwords for different stuff a client might have so I want to be able to put in as many as i want and then later search using the id number of the client.
`id` mediumint(9) NOT NULL auto_increment,
`description` varchar(50) default NULL,
`password` mediumint(9) default NULL,
`clientid` mediumint(9) default NULL,


comments table
just notes about the client.  I think i need a table for the comments so I dont have to append to the ones in the resuts table.  That would get messy really quick.
`id` mediumint(9) NOT NULL auto_increment,
`comment` text NOT NULL,
`clientid` mediumint(9) default NULL,


invoices table
This table has invoice info.  So I can create an invoice online and print it onsite.  Then be able to later search through all invoices attached to a client.
Havent set this one up yet......

Does all this make sense?  Am I headed in the right direction?  Any comments would be nice.

thanks,

http://www.soloenterprises.org
0
Comment
Question by:livegirllove
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:eeBlueShadow
ID: 11857448
That first table looks pretty chunky - if you separated client info from payment info then you could take more than one 'project' from each client.

`password` is a mediumint?

It's definitely along the right lines, you'll find out more as you build the interface for it whether you've done it well enough for your needs.

_Blue
0
 
LVL 5

Expert Comment

by:Gitcho
ID: 11857888
It's tough to know if your DB structure is adequte unless you've done all the pre-planning (ie. you know exactly how your application is going to use the database).  For limited use, what you've got is fine.  

The problem though, is that most people will start with something like this, and end up with far more work on their hands later when they realize they're using it more than they first thought.  

When I was taking all my DB admin courses, they pounded it into me to plan plan plan BEFORE you ever touched the DB.  It didn't take long to find out why - it becomes a pain to change it later, and it sucks to have to start seperating out your data when you realize you need more tables, or that you have hundreds of records of redundant data because of poor design.

Anyhow, to get your DB in 3rd normal form (getting rid of all attributes that are not dependent upon the primary key), you may want to seperate out some of the fields from your "results" table :

-----------------------------------------------------------------------------

`id` mediumint(9) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `email` varchar(100) default NULL,
  `aim` varchar(100) default NULL,
  `msn` varchar(100) default NULL,
  `yahoo` varchar(100) default NULL,
  `irc` varchar(100) default NULL,
  `ircalias` varchar(100) default NULL,
  `sitename` varchar(100) default NULL,
  `address` text NOT NULL,
  `address2` text NOT NULL,
  `city` text NOT NULL,
  `state` text NOT NULL,
  `zip` text NOT NULL,


TRANSACTIONS Table

`id` mediumint(9) NOT NULL auto_increment,
  'clientID' mediumint(9) NOT NULL,
  `paymentmethod` varchar(100) default NULL,
  `totalprice` varchar(100) default NULL,
  `amountre` varchar(100) default NULL,
  `deadline` varchar(100) default NULL,
  `startdate` varchar(100) default NULL,
  `completion` varchar(100) default NULL,
  `refered` varchar(100) default NULL,
  `comments` longtext,
  `packagename` varchar(100) default NULL,
  `payment` varchar(100) default NULL,
  `total` varchar(100) default NULL,
  `clientpaying` varchar(100) default NULL,
  `typeof` varchar(100) default NULL,
  `amount` varchar(100) default NULL,
  `referedby` varchar(100) default NULL,
  `comment` longtext,


Make sure your clientID in each table is NOT NULL.  Also, it's best practice to list your primary keys first, then your foreign keys, then the rest of your fields in each table.

If you are using "total" field for monetary amounts, you may want to use the FLOAT datatype instead of VARCHAR.  Same thing for AMOUNT, PAYMENT, TOTALPRICE etc etc.  This would make calucations a bit easier, and eliminate the need for conversion.

You can also perform calculations on the date fields, if you use date data-types, which would make it much easier to search later on  ...

0
 
LVL 1

Author Comment

by:livegirllove
ID: 11857893
thanks for the responses.  Thats just the type info I was looking for.
I never took a logic class or any DB admin classes so I'm missing that part.  Anybody got any good links to start me off?  Basic DB logic?

thanks again
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 5

Accepted Solution

by:
Gitcho earned 500 total points
ID: 11857920
0
 
LVL 5

Expert Comment

by:Gitcho
ID: 11865145
Does that info help ?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 11865490
Great links, Thanks.  I've just been reading through them.
See if I have any more questions then Ill close the topic.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now