Newbie Pre-Design Question

[JamieB]

Hi All-
I poked around existing threads for awhile and don't really see what I'm looking for. I appreciate any info that can be offered... and I apologize for my ignorance in database design and operation.



I have been reading Access 2010, The Missing Manual by Matthew McDonald to try and get a foundational understanding of how to approach database design. To that end, I have obtained plenty of understanding to get me going. The one point that troubles me, though, is the recurring sentiment that databases don't like repeated data. I'm trying to design a database to track semi-annual store inspections. By it's nature, this will mean some info will be repeated throughout the database (i.e. store number, region, inspection points, etc.). Granted, I'm planning on allowing the AutoNumber to designate each individual inspection as unique. I guess my question is this: does that provide sufficient uniqueness to overcome the fact that many, many of the fields will have recurring information?

I just wanted to proceed with care given that this is my first foray into database design.

Thank you in advance.

[Dal Jeanis]

Okay, repeating a foreign Key field is not repeating information. The Store table will have a unique Key for each store, your Employee (or Inspector) table will have a unique key for each employee.

Those keys will show up as a DATA field on each inspection record, to show WHO did the inspection WHERE. There will also, obviously, be a date field and so on. What you won't do is unnecessarily duplicate the Inspector's name, phone number, birthdate and so on on the inspection record.

Pop over to Access MVP Roger Carlson's site at http://www.rogersaccesslibrary.com/ and read his tutorials on database design. They're short, direct, and easy to read. Do a couple of the design exercises. After an hour or two of that, and you'll have a lot better overall understanding of the technical data that you're reading in McDonald's book.

[ck4794]

hey friend how is this working out for you? you sounded pretty lost. i would've just offered up information but i don't want to confuse you more, so is there anything you're having trouble with?

[JamieB]

hey friend how is this working out for you? you sounded pretty lost. i would've just offered up information but i don't want to confuse you more, so is there anything you're having trouble with?

Overwhelmed is more like it. I've always had an IT department, so I thought databases just built themselves once you submit a help ticket.

Seriously, I've taken on a mid-life career change and am trying to learn database design on top of it. I may have just been over-thinking a little... certainly a character trait of mine. To make matters worse, I won't have actual data to populate until next week when I start trying to test this database in tandem with the current scheme in excel. I'll get it, but there are sure to be some growing pangs. There's no pressure... I'm doing this for myself, really. I got them away from doing paper versions, so anything else is just gravy.

What I have so far is a one-to-many relationship. I have the "one" as a unique inspection number (PK) and have it as a foreign key in all the other tables. I'll know shortly if it'll work. May need tweaking, but therein lies the learning, right?

Once I can see it with real data, I may be able to ask an intelligible question. But I do appreciate you asking.

[Dal Jeanis]

I may have just been over-thinking a little... certainly a character trait of mine.

Welcome to the forum. You'll fit right in.

Do the tutorials over at Roger's Access library, and you'll feel more comfortable about your progress.

The one (non-obvious) rule I tell beginners is this - if there's a choice between methods, and the experts tell you one way, but you fully understand a different way, do it the way you understand. You're the one that has to support the thing.

Do try to understand the experienced guy's (gal's) way, but if you can't GET it, you're better off using code where you know what each line actually does.

[JamieB]

The one (non-obvious) rule I tell beginners is this - if there's a choice between methods, and the experts tell you one way, but you fully understand a different way, do it the way you understand.

Seems like sage advice. Thank you.

[ck4794]

Overwhelmed is more like it. I've always had an IT department, so I thought databases just built themselves once you submit a help ticket.

You just got promoted

Once I can see it with real data, I may be able to ask an intelligible question. But I do appreciate you asking.

In this phase I usually have a few "test" records with the kind of data i think i'll be getting. Problem is just make sure they're all gone when you publish. more importantly if you do it in the middle of an update after you have your data. i.e. getting frustrated cause it's not working, creating profiles with inappropriate names, which i won't mention here, forgetting to take them out, and saying "here boss it's all done"

[Dal Jeanis]

creating profiles with inappropriate names, which i won't mention here, forgetting to take them out, and saying "here boss it's all done

Civilians have no idea how many times that happens...

[ck4794]

what do you mean civilians haha?

[Dal Jeanis]

Regular people. The uninitiated. People who don't "get" Dilbert. In Rennaisance Faires, they're called mundanes. In the military, civilians. In Scientology (or old England), wogs. At carnivals, rubes. At Gypsy gatherings, gorgios. And so on...

In this case, those who are blissfully ignorant of the dark underbelly of technology.... Muwahahahahahaaaaa

[ck4794]

Regular people. The uninitiated. People who don't "get" Dilbert. In Rennaisance Faires, they're called mundanes. In the military, civilians. In Scientology (or old England), wogs. At carnivals, rubes. At Gypsy gatherings, gorgios. And so on...

In this case, those who are blissfully ignorant of the dark underbelly of technology.... Muwahahahahahaaaaa

in my case military haha. and now that you mention it... dilbert, yes... 'nuff said.

[ck4794]

so mr. initiated, i'm gonna hijack this guy's thread, and i'm gonna justify it by saying "maybe he'll get an idea". so i chose to operate with a main form for a person's "profile", and to view their respective documents via subform. nothing too complicated, and not really too relevant now that i think about it. but anyways, i chose to use the pop-up method of adding a new document (read attachment). i can't for the life of me seem to figure out how to make the profile form requery after i close the upload pop up. any suggestions?

also i work in macro builder, and query design windows. when it comes to code, i think i qualify as "initiated", but not much more than that.

[Dal Jeanis]

Yeah, that deserved a new thread. Make one and I'll comment there.

[ck4794]

oh all right... i posted it over in the forms section...