Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Idaho
    Posts
    8

    Database Design Ideas, Etc

    A database I need to do for someone, pretty small really compared to all the big projects everyone has. It has some stuff to need to do this.

    Store info From a client
    Client info: Client_ID, Client_name, Address, Phone, Group

    Store info about services recieved
    Services: Hearing Aids, Suits, glasses
    wanted a selectable set for most common stuff, but to be able to add one or two that is not there - along with prices used for each client

    Successful closures : closed or not closed status

    Store info about forms.
    Form_info: 1-12, recieved, not recieved, they had also wanted a fillable pdf file associated, saved, and able to be printed with each. one idea i had for this was to use php, and then have a linkt o save as pdf. but other ideas are accepted

    A place for notes about each client, for years of notes.

    They had wanted a set of queries to be run, like say, cost per client based on services recieved. sorted by groups, etc.

    They had also wanted to be able to print address labels for mailings from the address stored in database

    Also, as far as a front end, something to just point and click for stuff, and to add stuff, since they dont want to be bothered by the small details of it, what would work good? to just use a webpage type interface on a Lan, or create a program to do it?

    Thanks, any other questions just send a message. Theres a little bit more info to it, but not much more

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Client(ClientID, ClientName, ClientAddress, ClientPhone, GroupID)

    Group(GroupID, GroupName)

    Service(ServiceID, ServiceName)

    ClientService(ClientID, ServiceID, PriceAmount)

    Explain "Closures"

    Note(NoteID, ClientID, NoteDate, NoteText)

    Don't understand "Form Info"

    That's about all I can make out about the tables. Some of your questions are out of the scope of this forum.

  3. #3
    Join Date
    Jan 2004
    Location
    Idaho
    Posts
    8
    Well ok, the closures, will simply be, is the case open or closed, as far as the form info, there needs to be like.. yes or no for each of the 12 forms, to tell if the forms are on file, they wanted it to be editable and saved as a pdf and able to be printed, but what i was thinking was through php with the pdf and have them fill out the form online, and perhaps saving each part of the field as a seperate entry in the database. Also, what Database software would be best, and what would be the best programming language to use for a fornt end, something effiecient, quick, and simple.. something like.. they click this button.. it runs this query and tells what ever the query was. like.. a button that says.. show closed cases. and it shows all the closed cases versus the open ones..
    Thanks,
    Daniel

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I'm still vague on closures. Is this the closure of a service a transaction a set of client services. There seems to be a table needed, but I'm not sure how to implement it. Maybe this:

    Transaction(TransactionID, IsClosed)

    ClientService(TransactionID, ClientID, ServiceID, PriceAmount)

    As far as form info, you say there are 12 forms. And all you want to capture about the forms is whether they have been received or not and the path to the pdf file stored on your system.

    I can't tell you what front end to use. It does seem that a web interface to capture data would be better than a pdf form. The data could be printed out in any format you wanted. If the app is not too big I'd suggest MS Access and VB.NET. I invite anyone else to take this question on. Note, though, that the simpler you want the front end to be the more work will be required at the back end.

    Just out of curiosity, how many transactions do you think you will be handling over say, one year?

  5. #5
    Join Date
    Jan 2004
    Location
    Idaho
    Posts
    8
    hmm ok the closures is pretty much. is this client still here yes or no. Over the course of a few years.. right now only about 83 clients but in 3 years might reach up to 100 clients, so client records wont get too big with about 5 people accessing it over a network, in order to retrieve data and input data about them, so not too much going on really. thats about correct for the forms issue.
    Pretty much costs and and enduser simplicity are the 2 factors.

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    How many tables and fields do you estimate the forms would require if you converted to a database?

    What volume of forms would you be processing?

  7. #7
    Join Date
    Jan 2004
    Location
    Idaho
    Posts
    8
    The forms.. probably 20 fields each give or take a few.. so.. 12 for each client. so in the end.. after its all put in.. about 1000 forms.. tables and fields.. as many as it is effiecient

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    It should be easy to put together a web app that can capture and store this data in a database. It should also be easy to do this with freeware or shareware.

    This is not my domain. Can anyone make suggestions?

  9. #9
    Join Date
    Jan 2004
    Location
    Idaho
    Posts
    8
    Originally i was thinking Php and Mysql to get it done. since i know some about those, though could quickly learn anything else

  10. #10
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think php and MySQL are a good bet.

  11. #11
    Join Date
    Jan 2004
    Location
    Idaho
    Posts
    8
    K sounds good.. thanks for the help

Posting Permissions

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