Is this design conceot right?

[popellis]

Title was suppose to be "Is this design concept right?"

I have been a newbie since version 5.5
Enclosed I have posted a diagram of the way I thought my database should be designed.
Since I have never got it to work the way I thought it should be. I am starting over from scratch for the 6th or 8th time.
So I thought this time maybe I should look more closely at my design, that it might be right for what I am trying to accomplish.
That my workflow is getting all cluttered, trying to figure out the use of tabbed forms, versus the use of a single form and browses.
Side Note: "I guess in a flat file all the fields are in one table, and all information ends up being duplicated, But in a relational database information is spread out over many tables and trying to bring them together, "For Me" this gets confusing."

My main objective is to:
Keep track of Customer information
Keep track of the jobs we perform for each customer
keep track of the time it takes to complete these jobs
keep track of the materials used on each job.

End result
to be able to go back and look up any customer and find out what jobs we have done for them.

Print an invoice with customer information and the information about the job and materials used.

to be able to create a new invoice with the customer information already filled in.

Could I ask for some guidence on what I am doing wrong.

I would really like to complete this task, as this is the simplist of the task I have to do, but I keep hitting a wall.

Thanks

[popellis]

Re: Is this design concept right?

Another thought or question I have for the plumbing jobs set.

Should the relationship between "customers table" and "jobs table" be 1 to 1 for data entry and 1 to many for reviewing?

[Tom Cone Jr]

Re: Is this design concept right?

Mark, the set & table designs look ok to me. They will work well for your reports. However, I would not use either one for my primary dataentry form(s). I favor the use of simpler sets, with fewer tables, and more forms. My design polestar is Dr. Wayne's article "Simplify your Application for better Performance" at www.learn alpha.com.

-- tom

[Tim Kiebert]

Re: Is this design concept right?

Ditto to what Tom said.

The only thing I might change in your set/table design is the linking keys between the Job table and the Resident table. I would remove the Job ID field from the Resident table and add the Resident ID to the Job table. Then use the Resident IDs for the link. Since you specify a 1-1 relationship from job to resident I am assuming that a job can only apply to one resident. So having the Resident ID in the job table answers the question, "What resident is this job for. This would also allow you to reverse the set and ask the question, "What other jobs have been performed for this resident. (much the same as the customer - unless I have completely misinterpreted the resident table. :))

[Tom Cone Jr]

Re: Is this design concept right?

Good point, Tim.

[Peter.Greulich]

Re: Is this design concept right?

Is a resident the same thing as a customer? Or is your customer some company that gives you several residents to perform work at?

One thing I noticed on you actual data that you posted in another thread, your ID fields seem to be rather long, such as C16. I would keep the lengths in line with you actual work. My rule of thumb is make your ID fields as long as the maximum number of records that you realistically expect to have, multiplied by 10. So, for example, if you expect over the lifetime of you business to have 5000 customers, round that up to 10,000, and multiply by 10 = 100,000 . That will give you a C6 ID field length (which gives you 999,999 records). Of course, if you use a precursor character, you will need to allow for that in addition. I would not use precursors such as "Cust-", as I believe you have done in your posted tables; that just complicates things.

[popellis]

Re: Is this design concept right?

Tim

 I would remove the Job ID field from the Resident table
 and add the Resident ID to the Job table. Then use the Resident IDs 
for the link. Since you specify a 1-1 relationship from job to resident
 I am assuming that a job can only apply to one resident.

Okay, I'm a little confused, so forgive my ignorance if I ask. Don't you end up with the same result if this is a 1 to 1 relation ship and the linking field is in both tables. What makes your way that much different than the way i did it. Again forgive my ignorance.




Peter

 I would not use precursors such as "Cust-", as I believe you have done
 in your posted tables; that just complicates things.

I did this to help me understand which ID # I was looking at, and you are right they started giving me trouble right off the bat.

Tom

I favor the use of simpler sets, with fewer tables, and more forms.
 My design polestar is Dr. Wayne's article "Simplify your Application for better
 Performance" at www.learn alpha.com.

I will have to look at this article. Every thing I have read seems to be the opposite, it is breaking you data down into smaller bits or more tables to keep redundancy of data down.
How would you divide up the data presented? and Why would you do it that way?
I guess it's evident that I need more information on design. If there is a good article on design that you could point me to I would appreciate it, but it would have to more suited for a average joe, than an experianced data programmer.



Thank you all for your responses
Sorry I have to go for now as I have company.