[dand1981]

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

[certus]

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.

[dand1981]

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

[certus]

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?

[dand1981]

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.

[certus]

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?

[dand1981]

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

[certus]

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?

[dand1981]

Originally i was thinking Php and Mysql to get it done. since i know some about those, though could quickly learn anything else

[certus]

I think php and MySQL are a good bet.

[dand1981]

K sounds good.. thanks for the help