Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Post Unanswered: Advice -Time Sheet + Invoice

    Hi Guys

    Need some sort of advice.
    Basically, I like to build a DB for the following purpose:

    - To add new employee details
    - To add new customer details
    - To generate a time table sheet for each employee
    - To generate an invoice for a particular customer with the emp working hrs etc

    I kind of draw a sketch on a piece of paper:

    Employee Table
    - ID as PrimaryKey
    - Name
    - Address
    - Contact Nr

    Customer Table
    - Company Name as PrimaryKey
    - Address
    - Contact Nr

    Working Table
    - ID as PK
    - Date
    - Hours
    - Breaks


    Invoice Table
    - ID as PK (invoice ID)
    - Date
    - Employee Name*
    - Customer Company Name*
    *** WORKING HOURS AND DATES OF THE EMPLOYEE*


    This is getting a bit tricky ...
    any advice, would appreciate


    cheers

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you know what employees have worked what hours and for which customer
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24

    hrs for Co.

    Right...
    Working Table
    - ID as PK
    - Date
    - Hours
    - Breaks
    - COMPANY ID

  4. #4
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by healdem View Post
    how do you know what employees have worked what hours and for which customer
    Well, if you write them down on a paper.... you keep a record
    but putting that system into DB, is getting irritating

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    the question is LOOKING at your db design
    how do you know what employees have worked what hours and for which customer
    taking a step back from 'putting that system into a DB', do you think your paper system can generate invoices from the design as currently outlined. its all well and good defining PK's, buit I see no relationships (no FK's/ or PFK's)

    when designing your table and or columns stick to either underscore or camelcase, dont' use spaces. although spacers are legal in Access they are a pain over time, and will also cause problems if you use another data storage medium.

    underscore:-
    company_name
    camelcase:-
    CompanyName

    another good habit to get into is to name your tables as plurals (eg Employees, Customers....)
    there's as many namng conventions as, seemingly, there are developers, but have a look at this:-
    https://www.google.co.uk/search?q=as...m=122&ie=UTF-8

    the reason for using standards is an attempt to make tables and column self documenting, other developers, not just you can easily understand the table design

    a lot depends on how yourecord an employhees timesheet.
    do they clock on/clock off on the system (IE you have know precisely when an event happened), or are your retrospectively recording the number of hours an employee has worked.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by ranman256 View Post
    Right...
    Working Table
    - ID as PK
    - Date
    - Hours
    - Breaks
    - COMPANY ID
    Ahh yeh, forgot about that, cheers

  7. #7
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by healdem View Post
    no
    the question is LOOKING at your db design


    taking a step back from 'putting that system into a DB', do you think your paper system can generate invoices from the design as currently outlined. its all well and good defining PK's, buit I see no relationships (no FK's/ or PFK's)

    when designing your table and or columns stick to either underscore or camelcase, dont' use spaces. although spacers are legal in Access they are a pain over time, and will also cause problems if you use another data storage medium.

    underscore:-
    company_name
    camelcase:-
    CompanyName

    another good habit to get into is to name your tables as plurals (eg Employees, Customers....)
    there's as many namng conventions as, seemingly, there are developers, but have a look at this:-
    https://www.google.co.uk/search?q=as...m=122&ie=UTF-8

    the reason for using standards is an attempt to make tables and column self documenting, other developers, not just you can easily understand the table design

    a lot depends on how yourecord an employhees timesheet.
    do they clock on/clock off on the system (IE you have know precisely when an event happened), or are your retrospectively recording the number of hours an employee has worked.

    Hey buddy

    Regards the naming, to be fair I simply put it here in very simple terms,
    just to give u guys the idea what I am talking about. Of course, when it comes to DB design, I would stick to like "CompanyName, EmployeeSurname," etc you know

    I am just trying to get my head around on making a "working" system on paper and transferring to DB , or maybe I should just do it on DB right away.


    To give u a better overview, I am basically a "middle man" finding workers for somebody else ... so I get paid by the company, and "my workers" get paid by me. is like a type of agency if u get me.
    So, I will keep a record of how many hour my workers have worked and where I have sent them to work for. (I assume the company will do the same, keep a record!). and then, depending on how we agree on : we sent a weekly or monthly invoice to the company showing the working dates/days/hours of "all the workers I have seen to him" and then asking a total price + vat. Eventually, all this can be done on paper ... but I thought of using a system and saves me a lot of "awful" handwriting and paper.


    would be good if you tell me precisely where I gone wrong in the design,
    just ignore the naming convention at minute, as I will do the right away later.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    gimme's usually don't resolve the problems the asker is gittign. yes in the short term you may get a fix for say one table, but you have';t necdesdarily understood the principles.

    again Id suggest you look at your dewsign and try and work out if you can achieve the desired outcomes

    if needs be ask yourself all manner of questions

    how do I know what employee is working for what customer
    how do i know that I have captured the data properly
    how do i stop duplicate and or missing entries
    how do i.....

    a gimme answer may well solve the immediate issue, but it doesn't equip you with the tools to make a reasonable job of getting you up to speed with the concepts, the reasons why you design a db in a particular style. why you employ a specific approach

    if you have enough experience and a deep enough grasp of the db fundamentals then you can design a db directly in Access. however Access is 'too easy' to to create 'stuff'. without adequate experience its all to easy to design stuff that is poorly thought out, poorly implemented and frankly a mess.

    large complex db's should nearly always be drawn out as ER diagrams and so on. however my weapon of choice for that sort of thing is SQL Architect or MySQL Workbench and or a UML tool. if the application is being developed by multiple developers then its very rare to go straight to table design
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2013
    Posts
    72
    Ok after all the critics, this is what I done:


    Employees Tb
    - EmployeeID *PK
    - EmployeeName
    - EmployeeAddress
    - EmployeeContactNr

    Customers Tb
    - CustomersCompanyName *PK
    - CustomersAddress
    - CustomersContactNr

    Working Tb
    - CustomersCompanyName *FK
    - EmployeeID *FK
    - WorkingDate
    - WorkingHours
    - WorkingBreaks


    Invoices Tb
    - InvoiceID *PK
    - InvoiceDate
    - CustomerCompanyName *FK
    - EmployeeName *FK
    - WorkingDate
    - WorkingHours
    - WorkingBreaks
    - TotalHours <-- should SUM the total hours, maybe this belongs in Report
    - TotalInvoice <-- same! Belongs in Report showing total invoice



    Any comments??
    Last edited by Bucki; 05-02-14 at 11:29.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Irs still the same observation
    how do you know (identify) what employee is working for which company
    there should be no need for an idcolumn in your working table as a composite key if employee and date unuquely identifies the working hours fir a specific employee on a specific date. As an added advantage it also means you cannot have duplicate hours booked
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by healdem View Post
    Irs still the same observation
    how do you know (identify) what employee is working for which company
    there should be no need for an idcolumn in your working table as a composite key if employee and date unuquely identifies the working hours fir a specific employee on a specific date. As an added advantage it also means you cannot have duplicate hours booked
    Ahhhh instead, of the ID in the working tb
    I would have the companyName as a *FK instead

    this way, it should tell which person worked where and the dates/hrs etc

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    on the face of it the invoices hours worked is derived data (ie shoudl come from the working table. howevr for an invoice, which is a separate legal document so its not derived. however I doubt your cusotmers would care about no of hours an employee took as breaks. they are paying so much per hour.

    however as with your earlier tyr db I guess it all depends on how much this application is meant to model a real world problem
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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