|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 7th, 2009, 03:42 PM
posted to microsoft.public.access
|
|||
|
|||
Table Design Advice
Hello, I am a database novice. I would like someone to explain the
pros and cons of each design below: User uID, uLastName, uFirstName ==== Design A ==== Access List alMonthlyID, alUserID, alYear, alType Monthly mID, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct, mNov, mDec ==== Design B ==== Access List alMonthlyID, alUserID Monthly mID, mYear, mType, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct, mNov, mDec My first choice was Design A. Mainly because, alUserID will normally have multiple years and types. For some reason, the year and type fields seem to apply or rely on the user more so than the set of months. From what I have read, adjectives == fields, so I think I have that part correct. I am just wondering if it is okay to place them in an outer-related table? I am now at a road-block with this, and I cannot decide which that I need. Below is a sample output for 1 user. John Doe 2008, Basic $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $17.99, $17.99, $17.99, $17.99 2008, New -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, $0.00, $0.00, $0.00, $0.00, $0.00, $0.00 2009, Basic $17.99, $17.99, $17.99, $17.99, $17.99, $17.99, $19.99, $19.99, $19.99, $19.99, $19.99, $19.99 2009, Write $3.99, $3.99, $0.00, $0.00, $0.00, $3.99, $3.99, $3.99, $3.99, $3.99, $4.99, $3.99 TIA! 
|
|
#2
July 7th, 2009, 04:02 PM
posted to microsoft.public.access
|
|||
|
|||
|
Table Design Advice
"Designs" are suited to solving (business) problems/issues. What business
issue are you trying to solve? NOTE: if you use columns named after months, or other "repeating fields", you have a spreadsheet, not a relational database (i.e., Access). This design works well for spreadsheets, but causes all kinds of maintenance headaches if you try to force Access to eat 'sheet data. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Gusero" wrote in message ... Hello, I am a database novice. I would like someone to explain the pros and cons of each design below: User uID, uLastName, uFirstName ==== Design A ==== Access List alMonthlyID, alUserID, alYear, alType Monthly mID, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct, mNov, mDec ==== Design B ==== Access List alMonthlyID, alUserID Monthly mID, mYear, mType, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct, mNov, mDec My first choice was Design A. Mainly because, alUserID will normally have multiple years and types. For some reason, the year and type fields seem to apply or rely on the user more so than the set of months. From what I have read, adjectives == fields, so I think I have that part correct. I am just wondering if it is okay to place them in an outer-related table? I am now at a road-block with this, and I cannot decide which that I need. Below is a sample output for 1 user. John Doe 2008, Basic $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $17.99, $17.99, $17.99, $17.99 2008, New -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, $0.00, $0.00, $0.00, $0.00, $0.00, $0.00 2009, Basic $17.99, $17.99, $17.99, $17.99, $17.99, $17.99, $19.99, $19.99, $19.99, $19.99, $19.99, $19.99 2009, Write $3.99, $3.99, $0.00, $0.00, $0.00, $3.99, $3.99, $3.99, $3.99, $3.99, $4.99, $3.99 TIA!
|
|
#3
July 7th, 2009, 04:11 PM
posted to microsoft.public.access
|
|||
|
|||
|
Table Design Advice
Neither is correct. In both cases, your Monthly table has repeating
columns, that is, columns which hold much the same type of information, but distinguished by the value of the column name. This is a very fundamental error which will make some types of queries impossible. Your Monthly table should be something like this: Monthly mID, mYear, mMonth, mValue (whatever "Value" represents, which you haven't said.) So, with data, it would look like this: 1, 2008, Jan, $15.99 2, 2008, Feb, $15.99 3, 2008, Mar, $15.99 BTW, I'm not saying this is the correct design, because you've left out a lot of information about what things really mean. I'm just giving you an example of how you can correct the repeated columns problem. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Gusero" wrote in message ... Hello, I am a database novice. I would like someone to explain the pros and cons of each design below: User uID, uLastName, uFirstName ==== Design A ==== Access List alMonthlyID, alUserID, alYear, alType Monthly mID, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct, mNov, mDec ==== Design B ==== Access List alMonthlyID, alUserID Monthly mID, mYear, mType, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct, mNov, mDec My first choice was Design A. Mainly because, alUserID will normally have multiple years and types. For some reason, the year and type fields seem to apply or rely on the user more so than the set of months. From what I have read, adjectives == fields, so I think I have that part correct. I am just wondering if it is okay to place them in an outer-related table? I am now at a road-block with this, and I cannot decide which that I need. Below is a sample output for 1 user. John Doe 2008, Basic $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $17.99, $17.99, $17.99, $17.99 2008, New -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, $0.00, $0.00, $0.00, $0.00, $0.00, $0.00 2009, Basic $17.99, $17.99, $17.99, $17.99, $17.99, $17.99, $19.99, $19.99, $19.99, $19.99, $19.99, $19.99 2009, Write $3.99, $3.99, $0.00, $0.00, $0.00, $3.99, $3.99, $3.99, $3.99, $3.99, $4.99, $3.99 TIA!
|
|
#4
July 7th, 2009, 04:47 PM
posted to microsoft.public.access
|
|||
|
|||
|
Table Design Advice
I would like to thank both of you for the quick replies.
Both of which are pointing me to go in a different direction. I was unaware of "repeating columns" as being a no no. The data that I am working with originates from an excel sheet, as you might could have guessed. Let me re-think my approach, and I will come back with another question 
|
|
#5
July 8th, 2009, 06:22 PM
posted to microsoft.public.access
|
|||
|
|||
|
Table Design Advice
There's somehting else at work if your little DB is 4.5 meg. Most likely you
just need to "compact and repair" Your application is probably better off done in Access IF you wish to make the effort to learn Access. From a pure DB design standpoint, your new design looks fine. But the open question is whether or not it is storing the info / doinig what you need it to do. It's important to recognize and choose what "entities" you are databasing. In your new structure, these a Employees Instances of Monthly Payments for Insurance (and with the only informaiton about what insurance (or insurance policy) that it is for being the words "Health" or "Dental". If that's all that you need, you're fine.
|
|
#6
July 8th, 2009, 08:59 PM
posted to microsoft.public.access
|
|||
|
|||
|
Table Design Advice
Thank you for the input Fred. It ensures some of my reasons for
choosing to create the Access DB. I suppose it was rather silly of me to ask the opening question from my last post here. Sure, someone could answer it, but I am starting to realize the value of being able to answer that question for myself. I'm not exactly sure what was causing the file to be 4.5mb, but after creating a new database from scratch, it seems to have lowered some. It now includes all of my data which is almost 2,600 records, and the file is right at 4.8mb. I may or may not be able to get that smaller, but this is not an issue right now. I feel more comfortable now than I did when I started. Much thanks to you all! On Jul 8, 1:22*pm, Fred wrote: There's somehting else at work if your little DB is 4.5 meg. *Most likely you just need to "compact and repair" Your application is probably better off done in Access IF you wish to make the effort to learn Access. From a pure DB design standpoint, your new design looks fine. *But the open question is whether or not it is storing the info / doinig what you need it to do. * It's important to recognize and choose what "entities" you are databasing.. * In your new structure, these a Employees Instances of Monthly Payments for Insurance *(and with the only informaiton about what insurance (or insurance policy) that it is for being the words "Health" or "Dental". *If that's all that you need, you're fine. *
|
|
#7
July 9th, 2009, 02:58 PM
posted to microsoft.public.access
|
|||
|
|||
|
Table Design Advice
Access DB's get blank space in them and grow, e.g. when you make and delete
objects like forms, reports etc, their blank space remains. Running "Compact and Repair" squeezes all of that extra air out of your DB Either way I wouldn't be worrying about a 4 meg file. Multiply that by 500 and then you have a problem.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
