SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting off on the right foot...

    OK - I've finally committed to building a web based invoicing system for my clients. The ultimate goal being to be able to keep track of and manage my clients, and also provide a means for a customer to be able to view their accounts in a secured area of the website.

    Now, since a correctly designed database is paramount to the success of any application, I've put together the following design:

    http://www.homelanconsulting.com/images/layout.gif

    Can you all think of any ways to improve what I've put together? Are there any things you would change? I'm looking for as much feedback as possible before getting started with this.

    thank you,
    brian.

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about some means of timelining a project so that your client can see at what point what has been done along the way and why they've been billed when they've been billed. I was thinking about that and I reckon it's a good idea.

    G

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by bvarvel
    Can you all think of any ways to improve what I've put together? Are there any things you would change? I'm looking for as much feedback as possible before getting started with this.

    thank you,
    brian
    Not knowing what your business is about, I can only ask questions that hopefully will throw up some red flags.

    1. Do you always deal with just one person for a company? Do they only have one location? If either of these are false, then I would suggest creating a company table and then adding a contact table to handle the multiple contacts.
    2. Does your contact only have one phone number and one email address? If not, you may want to account for that too.
    3. Are your jobs always all-inclusive? Are there ever phases to a project?
    4. How do I calculate the project cost based on your layout? Is it purely by product? No labor included?
    5. What's the difference between price and cost on the products table?
    6. Is trans the same as payment amount on your payments table? I would also suggest you track method of payment (cash/check/cc) and track check nos and partial CC numbers where appropriate. Makes it much easier to balance later.
    7. Do you want to be able to show the customer the status of their job?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's food for thought:
    1. Yes - I always deal with one person / one location.
    2. Customer may have multiple email / phones, so i need to account for that.
    3. Jobs are usually all inclusive, but that doesn't mean I won't phase out a project in the future. - I'll need to account for that as well.
    4. Both labor and products must be accounted for in any project. The products table will also include labor items.
    5. Price is what is charged to customer. Cost is what I pay for any given product.
    6. Trans filed is indeed payment amount. Tracking payment method and payment info is not something I had thought about. This will be imperative!
    7. Yes - part of the point of this project will be so customers can view their accounts - both completed projects and ongoing projects - and their respective costs.

    This is getting to be a huge project! Which is exactly why I wanted feedback on the databases before I even got started! Thanks so much for your comments!

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by bvarvel
    This is getting to be a huge project! Which is exactly why I wanted feedback on the databases before I even got started! Thanks so much for your comments!
    I'm just glad you are doing the work up front. A lot of people slap stuff together and figure "oh, I'll fix it later..." well, that later ends up being a lot of work (usually for someone else....like, oh, ME!!)

    A lot of time up front will save more time later.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Naming convention

    I have one small observation on the database model you posted: consider database-wide key naming, rather than table-wide. You have given each table a primary key of 'id', so then then each table that references a foreign key to those primary keys must use a different name. I understand that this can be done with a consistent naming convention, but I think it introduces problems when your database gets large, or queries are complex: you have to mentally remind yourself what 'id' means in each context.

    I think it is a more "orthogonal" approach to name each key descriptively, and use that same name when referencing that key in another table. I know this doesn't completely rule out confusion in queries, but I think it goes a long way.

    In fact there are some that argue that every attribute in your database should be named uniquely, so that whenever you see a column name, you automatically know that it either 1) belongs to xxx table, or 2) is a foreign key to the attribute in that table. One plus of this approach is that joins can be much easier to write, since there is less need to create many aliased column names.

    A possible third way to look at things is to name every candidate or primary key descriptively, and to use a prefix or suffix when referencing it from another table, such as PK 'person_id', and FK 'person_id_fk', or some such.

  7. #7
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually have done that... the model I posted was an older version... didn't realize it until I went back and checked it. No two fields in any table have the same fieldname. Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •