New Database Startup

[AC5FF]

I've worked with tons of MS Access databases over the years, but never had the opportunity to start a new database from scratch. The DBs I have worked with have all had issues and when looking for help here I have always been told that I should "Normalize" the database.

Well, I want to get this one created 'correctly' from square one. Does anyone have any good links for walk throughs on doing this?

I'm building in essence a renter's database. But taking it a bit further. I want a tables for individual information, contact information, access information, Set Rules, etc. I've got a lot of 'learning' to do on how to enter this data, how to verify the data entry, etc etc... I figured that a good walkthrough website would be a great starting point.

Thanks!

 

[Alansidman]

this is probably a good place to start.

http://www.functionx.com/access/

Alan

 

[AC5FF]

Thanks Alan;

I've bookmarked the site and already found a useful bit of help there. Going to have to read up on relationships some; but I'll get there!

 

[gemma-the-husky]

the first thing you do is work out what data you need to store.

then you decide how this should be arranged in different tables

the table design should be normalised - which is the process of making sure that you do not store any redundant data - that is, data which can be determined without it being stored spearately, or in another place

while doing this, you can consider the forms and reports you need as they will help you make sure you do not forget anything, but the table design should be independent of the forms and reports.

data design is often a process of stepwise refinement - when you start to develop the application you may decide you need some more data fields, or you adjust your design.

if the data is right, it makes design of the forms and reports much easier.

so what are you renting?
and what tables do you think you need?

 

[AC5FF]

Been a busy couple days at work and haven't had much time for this project...

Dave; Normalizing - this is exactly why i want to start here versus from scratch by myself. But I am running into questions/issues already! Mostly along the lines of storing 'redundant' data in tables.

For instance. I have a "Renter" table that will store personal information - i.e. Name/Address. I want a separate table to store phone number information; mainly due to wanting the flexibility to store as many phone numbers as needed. (May seem out of the norm - but this is the direction I have been told to follow!) So; in my renter table I will have a field for First, Last, MI and then I am going to have an ID field that will take an auto number field, combined with the last name and first initial to form a unique renter ID (again, we're still in the Renter table). So now in my Phone table I am going to just have two fields; the RenterID (pulled from the Renters table) and a phone number.

My question is; storing the RenterID in both tables is necessary to link the two tables; correct???

As for other tables; I am going to have a passcode table for access codes, a rules table that will show the access rule (i.e. times) for each passcode, and a Activate table for listing dates that the RenterID is active (i.e. lease dates). I am also wanting to somehow set up an "Accessed" table to store date/time information anytime that a renter's information has changed (i.e. phone numbers added/deleted, lease dates updated, etc...)

Through all of this I figured the RenterID information is going to be in every table. I have set up three tables so far, and in relationships I wanted to set up "One to Many" links between them. However I have been unable to get that to work. Not sure why yet; still researching it..

However, any advice you can offer to my ideas so far would be greatly appreciated!

Thank You!

 

[jDa]

You are correct: any table that links to the Renter table must contain the RenterID as a field. And yes, this is redundant in a way, but that kind of redundancy is okay. What you would want to avoid, for example, is storing the renter's name twice (or more). For example, if you had multiple phone numbers for a renter, and you kept them all in the Renter table, you would need a different record for each number and each record would need to have the Renter's name. That's the kind of redundancy to avoid. So, you're right again: having a separate PhoneNumber table is the way to go.

One minor caution... You mentioned having a RenterID that is a combo of an autonumber and the renter name/initial. Did I understand that right? If so, just stick with the number and leave off the name from the ID itself. The number + name idea would make what is called an "intelligent key." Not good for the following reason: let's say you have a Renter whose ID is 123DoeJ. Now miss Doe meets the love of her life and changes her name to Jane Public. You would either need to change her ID (not good for a variety of reasons), or else put up with having an ID that no longer obeys your convention (how annoying).

As for the other tables, you would only need a separate table if multiple records in a table link back to a single record in the Renter table. For instance, if a person can only have one passcode, then a separate passcode table wouldn't be needed. Passcode would just be a field in the Renter table. But a separate passcode table would indeed be needed if each person may have multiple passcodes.

Hope this helps... Good luck and have fun!!

jDa

 

[AC5FF]

jDa

Thank you for the information. It's great to confirm what I am planning is correctly set up!

Anyone have any ideas why - on a sample database I download relationships can be and are set up as a 1 to many - but on my database I cannot do that. The only thing it'll let me set up is an "Indeterminate".
I've followed links/help instructions for setting up a 1 to Many relationship, but I never see an option for that and nothing ever changes to that...

 

[jDa]

Without seeing it, it is hard to say for sure, but the first thing I would check is primary keys and indexes. RenterID should be a primary key. The field linked to RenterID on the "many" side of the relationship must NOT be a primary key (however, it can be a part of a composit primary key--a primary key consisting of more than one field), and it must not be indexed so as not to allow duplicates. In design view, check properties to make sure, if it is indexed, it is set to "Duplicates OK." Or, if it is not indexed at all, then that's not the problem. To summarize, a one-to-many relationship can exist between two tables if one of the linked fields is constrained to be unique and the other is not.

 

[AC5FF]

jDa

You did it... Thanks! My Renter table had an auto-number field that I set as the primary key. However, I was linking the RenterID fields between tables. I went back and set the RenterID in the Renter table to my primary key and now all my relationships between tables have changed to One to Many..

Thank You!