First Database and Need Advice

[ilmchan]

I've been tasked create a database for my firm. This will be my first and my only training is following the books so please bear with me.

We need to keep track of our clients and their information:
1. The client (address & phone)
2. Their schooling (address/phone/instructor contact)
3. Their medical/hospital records (address/phone/nurse contact)
4. Their employment (address/phone/boss's contact)
5. And so on this goes

Initially, I built a single Contacts table and included a "category" field to note "school" or "hospital" but it didn't seem right according to the books I'm reading. Do I need to build separate tables for each type of contact? If I do, how would I treat new types as they are introduced...build another table when the time comes?

I think I just need a nudge in the right direction but I just can't seem to get it on my own. Thanks for your time!

Mike

 

[danvlas]

I guess you can't build one table and just indicate the category, because the categories are too different. OK, they have the same number of fields for now, the structure is similar, but their real-life meanings are different.
What if at a certain moment you will also need the diagnosis for each medical record? Having one single table will leave the 'diagnosis' field blank for all other categories. This is just an example...

Therefore my opinion is to have a separate table for each category.

The main idea is that whenever a 'category' represents a group of records having the same meaning, you need a separate table.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ilmchan]

Daniel,

Many thanks for your insight. That's probably the way to go even though as you say the tables would look the same in structure. I just wasn't sure since I haven't seen this type of database in any of the examples from the books I've read so far.

Mike

 

[JeremyNYC]

Wow. I don't do this too often, cause he's so smart, but I have to disagree with danvlas on this one. As far as I'm concerned, I don't care how different the categories are, they're just categories, and they can be indicated with a single field, keyed to a lookup table.

I _never_ have more than one person table in my databases. People are people. I may need to hang tables off of that table for different types of people, or let some fields be blank in a bunch of records, but I always make my tables model real-life entities.

Mike, I would suggest you check out Paul Litwin's "Fundamentals of Relational Database Design." It's a fantastic article on how to structure tables. I recommend it so often that I've got a copy in the Developers' section of my website.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ilmchan]

Jeremy,

I will definitely look at ur site tonite as I'm still at work right now. Please bear with me as I will have more questions as I work thru this.

Thank you for your input!
Mike

 

[danvlas]

Jeremy, thanx for your appreciation, now I 'm really thinking of getting myself a statue LOL

Disagreement is the source of progress (just like laziness).

They're just categories....hmmm, arguable.

If all categories will always share the same attributes, you're 100% right. They should be stored in a Categories table.

But if one category would need/have a special attribute at a certain point, keeping one table would flaw the design, as the table would contain a field (attribute) used only for that category.

And in this case, I assumed that:
- "schooling" will keep all school/course records a person might have taken (many records for a client)
- "medical/hospital records" will keep entire medical history of a client (many records for a client)
- "employment" will keep track of all job positions the client might have had.

Each of these categories would inevitably be defined by different attributes.
This is what I had in mind when suggesting the 'octopus' configuration.

But if any/all of the above would store just one record, then those fields should be included in the Client table allright.

It all depends on how you see it.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ilmchan]

Hi Dan,

I suppose I could have been more detailed in my description but both you and Jeremy are giving me good advice based on what I wrote.

I was only asked to create a database to track "where" the client has been. The actual details of their history would be in the memo field. My boss said we were only interested in the different stages of their lives. Much like a resume in life.

Armed with that, would that change either of your opinions? I'm still plowing ahead with the single table but I still have my separate tables also, just in case. Thanks again for your help!

Mike

 

[danvlas]

"My boss said we were only interested in the different stages of their lives"
Oh, just that? [LOL]

That sounds like a one-to-many relationship to me... One client usually has many stages in life..

A memo field in this case is the best way to break all the normalization rules, and I'm sure Jeremy will agree on that.

Your boss is a boss like any other boss: he doesn't know what he will require in the future. He just thinks he knows...

And when he'll ask you for a particular stage of a client's life, you'll have to buy a lot of aspyrine...or change the boss.

I'd still go for the 'multiple tables' solution...

I keep my medical records in one box, my kindergarden/school/university/ diplomas in another box, and all original employment documents are kept in a closet at the company I work for. All of them are mine, and I have lots of each. How more real could it be?

Regards,




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[JeremyNYC]

Dan,

To continue on, in the respectful way we've got going here...

I think that's real, but not what is being modeled. The database should model people and actions or interactions, not forms. Paper forms are denormalized reports of a life (that was fun to write). They don't capture _you_, they capture particular interactions you had with others (teachers, bosses, etc.). There's still a fundamental similarity between teachers, bosses, students, criminals, race car drivers, and clients. That's why I go with one person table.

Anyhoo...

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BobJacksonNCI]

Mike,

I've received EXCELLENT assistance here and in Microsoft's community newsgroups. (Copy/paste all of the following
into your browser address to look.)

http://support.microsoft.com/newsgr...LCID=US&ICP=GSS3&sd=GN&id=fh;en-us;newsgroups

And I KNOW that Daniel and Jeremy mean well...
(Plus, they don't know where I live 8-D )

But, since you're starting from scratch,
HERE'S what you should do:

Fire up Access WITHOUT selecting a database to open.
Select the round box beside "Access database wizards, pages and projects". Verbiage may vary, I'm looking at Access 2000. Select the databases tab, then select Contact Management. Tell Access which folder to put the results in and let 'er rip.

This will provide a basic structure to begin with and tables that are set up using good techniques. You'll have to add fields, tables, etc - but you'll have LIVE EXAMPLES to model.

And I do NOT mean any offense to Daniel or Jeremy!

It's just that I am much closer to being a rank beginner and this is exactly how I started my database to keep records on my Labrador Retrievers. It was fast and
easy to modify as requirements dictated.

Good Luck!
Bob

 

[JeremyNYC]

Uh, yeah. So your point is that it's better to take advice from a "rank beginner" than from two professional developers?

OK.


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BobJacksonNCI]

No, Jeremy, my point is that designing tables,
creating relationships, setting up indexes,
making forms, writing reports, etc is FAR MORE complicated than most people starting out realize.

Therefore, delving into data normalization isn't the best place to begin... Especially if Mike's employer is
more interested in production than art.

I've benefited from your web site, Jeremy, and appreciate the help provided. This is not the place to flame, so if you're really upset with me and you're not just having a bad day contact me at robertj_18840@yahoo.com.

Good luck, Mike!
Bob

 

[ilmchan]

Guys...7 people quit in my office in the last 2 days. I'm being pulled in every direction and this project is still expected to proceed. I respect all of your opinions and advice. If I don't respond right away you know why.

I think Bob is empathizing with me as a beginner and not as a slight to Jeremy and/or Daniel...that's how I choose to see it. I will take a look at the wizard as well and see if I can take anything from it. I am open to all suggestions.

You guys have given me a lot to digest and I really appreciate your willingness to help me!

Thanks again,
Mike

 

[fneily]

I'll throw in my half of cent. If you look at the original papers of relational database theory from the mid-'80's from the mathematical creators at M.I.T and such, the first "form" of normalization states that you cannot have variable length records in a table. So, if you have a record with an extra field, this would cause your table not to be "rectangular", in other words, variable length.
Then again, in theory, you also don't have "junction tables" or foreign keys. Tables are just related through Tao.

Neil

 

[danvlas]

Jeremy: I guess I see something different from you on this post.

I am not speaking about Client data (or person, or boss, or teacher). The client table seems clear to me and there is nothing to argue about it.
I am speaking about their schooling, employment and medical records.

I'll take my employment for instance:
1988 - 1989 - company in city 1
1989 - 1993 - company in city 2
1993 - 1994 - company1 in city 3
1994 - 1996 - company2 in city 3
1996 - 1998 - company3 in city 3
1998 - 1999 - company4 in city 3
1999 - present day - company 3 in city 3 (again)

If employment has to be retrieved in this way, it has to be stored in a separate table, not in Clients.

The question is:
Should there be one table to keep all schooling, employment and medical records or each of them should be a separate table?

My answer was (and still is): separate tables, because each of them can have certain particularities that are not applicable to the others. But if anybody can come up with a structure to be valid for all of them, then they should be kept all in one table.

Bob: The Database wizard in Access are meant to provide you with a basic structure for learning, not for production databases. They are just examples!
One question: when did you set up your database?

Mike: seven ppl quitting in two days looks like trouble...Take care!







Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[BobJacksonNCI]

Mike,

Bet you NEVER imagined you'd get this when you first posted!

When/if you run the wizard, be sure to look at the relationships screen. It shows how the tables are tied together and you'll need to understand and probably imitate that as you add tables. Access relationships only have an operational impact if you want a deletion in one table to automatically cause deletions of related records in other tables. But, they do give you a visual that can be helpful.

And, as if this fire isn't already burning brightly enough, my vote is also for more tables. The thing about databases is that it takes MUCH less work to design it appropriately from the beginning than to fix it later. Also, the original specifications may call for a thousand records, but when it works so well for that, the boss may go for hundreds of thousands...

Dan,

LOVE the animated emoticons! Got to read how to do that - right after I finish my .adp project.

I started my Puppy Records database a couple of months ago. My operation is small, one or two litters per year, so it IS manageable on paper. However, since my first programming language was assembler in 1971, I'm finding that I cannot remember the names of customer's spouses and children. That can be significant from both standpoints of manners and business. It IS a work in progress - the latest modification became necessary because people often have more than one e-mail address, and the wizard didn't address that.

In case I haven't already offended everyone but Mike, the difference between learning and production is blurry when you're talking Access. In production, you do the best you can with the knowledge you have and then it's on to the next project. That's why my personal database is so much fun... I can tinker with it to my hearts content.

Mike would likely welcome a polished database created by a professional developer. And the developer would enjoy the ongoing retainer to maintain it because advanced techniques are too complex and have too many nuances for novices to handle. With all due respect, I stand by my opinion. He's expected to develop the database and he's expected to maintain it. I think he'll do best starting with a structure created by a wizard.

And he'll certainly appreciate the help he gets here and at other support sites. Mike, if you'd like more support site addresses, please send me your e-mail address. Mine's robertj_18840@yahoo.com.

Good luck!
Bob

 

[JeremyNYC]

Bob,

Sorry. It _was_ a bad day. My response should have been more in tune with Dan's response to you. I see your point, but I think it's best to only use those to learn from, and even then it's iffy. There's a _lot_ of bad coding in those examples, and plenty of bad design.

==

Dan,

I see what you're saying, but this is the reason I think they go in different tables:

"Initially, I built a single Contacts table and included a "category" field to note "school" or "hospital" but it didn't seem right according to the books I'm reading. Do I need to build separate tables for each type of contact? If I do, how would I treat new types as they are introduced...build another table when the time comes?"

So maybe we're looking at it differently--I don't mean that people and organizations should be stored in the same table, I mean that all people should be in one table and all organizations should be in one table.

===

Neil,

Yes, that's the theory, but in practice, things are very different from that. In practice, there will be plenty of fields that have no data for some records, and this is just fine--it's certainly better than thirteen tables in one-to-one relationships, one for each field that gets occassional use. Also, that theory was developed approximately thirty generations ago, in computer time. That was back when storage and retrieval were quite expensive. Storage is now practically free, and retrieval is quite cheap.

==

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ilmchan]

I'm still working on it gents....will re-read everyone's posts and go with one of the recommendations. I can listen and agree with everyone's different points of view and get nothing done in the meantime.

I'll continue working on it this w/e and hopefully have enough to post for your critique. Maybe then, you'll have a better perpective of the project I'm working on.

Have a fun and safe w/e...thanks for your time! Mike