Proper Table(s) Layout

I have created a couple of Dbs which the table structure seemed to simply 
fall into place, it was just logical in my head.

That said, I am working on a new db and for some reason I am doubting myself 
and wanted a second opinion.

The db is basically a contract db to input all the info, and there is a lot 
of info, for each contract.  Where I am 'lost' is the fact that the contracts 
are broken into categories: clients, components,engineering, warantee...  For 
all of the components (with the exception of clients) there are a number of 
fields but only 1 entry per contract.

Basically my question is, should I be rolling out one huge table with all of 
the data or should I be creating a project table and then multiple category 
table with a 1-to-1 relationship?  My gut is telling me to go with a single 
table, but at the same time I have never needed to create such a big table 
and it feels wrong!

Thank you for your enlightenment on the subject.

QB
0
Utf
5/6/2010 1:24:01 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

3 Replies
578 Views

Similar Articles

[PageSpeed] 12

I would read up on the rules for database normalization.
If your different types of contract have substantially different data 
requirements, I'd put anything common in one table and anything specific to 
that type of contract in a separate table.
However, to an extent it depends on how the data will be accessed and used 
and also the volume of the data.
If you went with one table and then later needed to add a new type of 
contract, it could be a hassle whereas if you had separate tables it would be 
easier.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"QB" wrote:

> I have created a couple of Dbs which the table structure seemed to simply 
> fall into place, it was just logical in my head.
> 
> That said, I am working on a new db and for some reason I am doubting myself 
> and wanted a second opinion.
> 
> The db is basically a contract db to input all the info, and there is a lot 
> of info, for each contract.  Where I am 'lost' is the fact that the contracts 
> are broken into categories: clients, components,engineering, warantee...  For 
> all of the components (with the exception of clients) there are a number of 
> fields but only 1 entry per contract.
> 
> Basically my question is, should I be rolling out one huge table with all of 
> the data or should I be creating a project table and then multiple category 
> table with a 1-to-1 relationship?  My gut is telling me to go with a single 
> table, but at the same time I have never needed to create such a big table 
> and it feels wrong!
> 
> Thank you for your enlightenment on the subject.
> 
> QB
0
Utf
5/6/2010 3:27:01 PM
QB wrote:
>I have created a couple of Dbs which the table structure seemed to simply 
>fall into place, it was just logical in my head.
>
>That said, I am working on a new db and for some reason I am doubting myself 
>and wanted a second opinion.
>
>The db is basically a contract db to input all the info, and there is a lot 
>of info, for each contract.  Where I am 'lost' is the fact that the contracts 
>are broken into categories: clients, components,engineering, warantee...  For 
>all of the components (with the exception of clients) there are a number of 
>fields but only 1 entry per contract.
>
>Basically my question is, should I be rolling out one huge table with all of 
>the data or should I be creating a project table and then multiple category 
>table with a 1-to-1 relationship?  My gut is telling me to go with a single 
>table, but at the same time I have never needed to create such a big table 
>and it feels wrong!

Definitely one main table and several 1-1 tables.  Basically, this is
subclassing.  There's an article on Access Web that explains how to do it...
http://www.mvps.org/access/tables/tbl0013.htm

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
5/6/2010 6:35:38 PM
I'm with Dorian ...

Any time you build a "large" (i.e., wide) table, you risk having to maintain 
it (i.e., fix it), and any related queries, forms, reports, code, etc.

Even though it means more work now, normalizing your data structure at the 
beginning makes for less work ongoing.  Pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" <QB@discussions.microsoft.com> wrote in message 
news:A39C7873-63AC-45DE-8EB5-0AD210B60D3D@microsoft.com...
>I have created a couple of Dbs which the table structure seemed to simply
> fall into place, it was just logical in my head.
>
> That said, I am working on a new db and for some reason I am doubting 
> myself
> and wanted a second opinion.
>
> The db is basically a contract db to input all the info, and there is a 
> lot
> of info, for each contract.  Where I am 'lost' is the fact that the 
> contracts
> are broken into categories: clients, components,engineering, warantee... 
> For
> all of the components (with the exception of clients) there are a number 
> of
> fields but only 1 entry per contract.
>
> Basically my question is, should I be rolling out one huge table with all 
> of
> the data or should I be creating a project table and then multiple 
> category
> table with a 1-to-1 relationship?  My gut is telling me to go with a 
> single
> table, but at the same time I have never needed to create such a big table
> and it feels wrong!
>
> Thank you for your enlightenment on the subject.
>
> QB 


0
Jeff
5/6/2010 6:35:54 PM
Reply:

Similar Artilces:

Hiding a userform properly!
Please take a look at the code below which my workbook is using: '-------------------------------------------------------- Private Sub Workbook_Open() Userform1.Show End Sub '------------------------------------------------------- Private Sub UserForm_Initialize() MsgBox "Initialize stuff here" Userform1.Hide MsgBox "Okay, form1 is hidden now!" Userform1.Show End Sub '-------------------------------------------------------- So....obviously this code will not work. I'm guessing that the second call to "Userform1.Show...

Where can i find a football/Soccer table template to download?
I need a blank template, so that we can fill in the team names, and one that add's up the socres etc. ...

PROPER
I need to change the info in a column. Right now it is all uppercase and I need for it to be like a PROPER name. I tried the PROPER function and that is not working. I need to do the complete column. I am a novice at excel. Please help If the data is in A1, =PROPER(A1) and copy down, Regards, "Annie B" <anonymous@discussions.microsoft.com> wrote in message news:3b8e01c4a4be$c8587b80$a401280a@phx.gbl... > I need to change the info in a column. Right now it is > all uppercase and I need for it to be like a PROPER name. > I tried the PROPER function and that is...

Pivot Table for Inventory Database
I have an inventory database that has separate fields for receipts and dispersements. How can I make a pivot table to show the part numbers, the receipts and dispersements and a running balance? tia Have a look at the PivotTable info on Debra Dalgleish's site. http://www.contextures.on.ca/tiptech.html Scroll down to "P" section. Gord Dibben Excel MVP On Tue, 22 Mar 2005 19:54:19 -0500, "ridgerunner" <&il_utrll@msn.com> wrote: >I have an inventory database that has separate fields for receipts and >dispersements. How can I make a pivot tabl...

S l o w ! ! !
Outlook chokes on sucking down emails during certain time of the day. It just hangs there, or is excruciatingly slow It's not the internet connection (T1) or the firewall, which allows both inbound and outbound POP3, IMAP & SMTP freely with no bandwidth restriction. It's not the POP3 server, so says the Qmail guy. Is there anything I can do to speed up POP3 connectivity in my Outlook? Someone told me to disable Messenger. Anything else? Thanks a lot. ...

Error message when using Pivot Table
I get an error message when I try to manipulate a very large Pivot Table. The message says "Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable or to the page position. Alternatively, right click a field and then click Hide or Hide levels on the shortcut menu." I can't even figure out how to execute the remedies in the error message! I can manipulate other Pivot Tables from other ss just fine. And my colleagues have no trouble with this particular very large Pivot Table--just me. Is this a...

Images with transparent areas don't print properly.
I have a project to make an A4 poster with Images and text. 2 of the images have transparent backgrounds created in an imaging package (Paint Shop Pro) as .PNG files. When I insert these images into the Project they display fine but don't print properly - only a vague outline of the darker parts print. However on Print Preview the document looks perfect. The other non-transparent area images on the page print fine. I am using Publisher 2003 on Windows XP Pro printing to a networked OKI C5200N Colour laser printer on A4 size. What could be wrong here? I would suspect the printe...

Data file did not close properly #2
I have read all kinds of solutions to this error. Has any thing difinitive been offered that solves the problem. Appreciated Jerry There isnt a difinative solution as the problem can be caused by a number of differing things "Jerry" <jerry@home.home> wrote in message news:eRfw6Cs1IHA.5300@TK2MSFTNGP06.phx.gbl... >I have read all kinds of solutions to this error. Has any thing difinitive >been offered that solves the problem. Appreciated > > Jerry > "Jerry" <jerry@home.home> wrote in message news:eRfw6Cs1IHA.5300@TK2MSFTNGP06.phx.gbl...

Table cell background not showing
I made a html-page with microsoft Frontpage to use as an Outlook template. I saved it in Outlook as an .oft-file and looks good when I compose a new message. However, the receiver of the message will not see the background I have in one of the table cells. I don't know if this has anything to do with this problem but I discovered that you cannot have a background in an MS Word table cell. Any help will be appreciated. >-----Original Message----- >I made a html-page with microsoft Frontpage to use as an Outlook >template. I saved it in Outlook as an .oft-file and looks good when ...

Script for testing disk's state
Hi all, I want to check with a script if a given disk is onlineline or offline. Is it possible? "Andrea Caldarone" <andrea.caldarone@poste.it> wrote in message news:%23GCmyy2cKHA.1596@TK2MSFTNGP06.phx.gbl... > Hi all, > > I want to check with a script if a given disk is onlineline or offline. Is > it possible? You could use mountvol.exe to enumerate the drive letter of each partition, then check the disk label for each drive letter. If you cannot find the label of the given disk or partition then it must be offline. ...

List Box and Updating the table
I want a form with 24 list boxes that the user can have multiple selections for each box..I understand that I need to have a table that will hold results and a new row for each selection, what i need to know is how do i get the data from the List boxes populated into the table? Thanks. Lisa, there are several problems with this. First, You will have to open the form on Friday and come back Monday to actually see it. I once tried a form with only 8 list boxes and it took 2 to 5 minutes to load the form. It has to retrieve the data from the database for each list box, one at a time. ...

Number Format Problem Prevents Proper Sorting #2
I copied the values into a new column and pasted them "special" and it worked. Thanks for your assistance. Vic -- 65Stang ------------------------------------------------------------------------ 65Stang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15008 View this thread: http://www.excelforum.com/showthread.php?threadid=275679 Thanks for the feedback. Glad to help. Mike "65Stang" wrote: > > I copied the values into a new column and pasted them "special" and it > worked. > > Thanks for your assistance. &g...

Pivot Table problem, blank fields aren't being added
Hi, I have a Pivot table pulling data from another workbook. It's displaying a count for given values in 2 columns. It's fine for most of the data, but when one of the columns is blank, it shows a count of 0, even though there are items that are blank. If I double click on the field, it shows me a sheet that contains the data that isn't being counted. Is this a bug in the Pivottable? Is there a way around it? Thanks. Ant A pivot table can't count blanks. You could type a space character in those cells, or use a formula: ="" Then, they'll be counted. An...

View/Copy Pivot Table settings ?
I'm a relative Excel beginner and have only begun to use/understand Pivot Tables Using Excel 2008. Is it possible to view the settings one set to develop a pivot table report? And then is it possible to copy those settings/structure to create another pivot table report on another sheet/workbook? Thank you. -- Norm ...

How do I disable auto-crop? It's cutting off text in document!
I'm new to the One-Touch 9220 USB Visioneer scanner. When scanning my text documents, the scanner automatically crops an inch off all four margins on the page, so I'm losing text. I see a pop-up message by the icon that "auto-crop scan" is in progress. How do I disable "auto-crop" when I need to? Thanks ... This is the Visio forum, not the Visioneer Scanner forum. : ) -- Hope this helps, Chris Roth Visio MVP "E-Jane-Back" <E-Jane-Back@discussions.microsoft.com> wrote in message news:9B768C06-FF15-4B7B-9254-E87B88F618F6@microsoft.com... ...

Page Layout Question
Hi - I am trying to create an invitation that is a half of a page. I want the half page folded in half to show a picture on the front and then when you open it, the details on the page on the right. I can't figure out how to set this up...everything that I try makes it so I have to fold the page in fours instead of just in half. Any suggestions on how to set it up this way? Thanks! Set it up as a booklet. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "Carol" <anonymous@discussions.microsoft.com&g...

Macro on a Pivot Table
I need to write a macro that takes the results of a pivot and creates detail tabs for all results that are greater than or equal to 20. I was able to record a macro that created the detail tabs for cells that I specifically double clicked on, but the data will change monthly and I need this to select only applicable cells. Any suggestions? Excel 2007 PivotTable List source details that have PT results of 28<=x<=32 http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_22_09.xlsm ...

Let's go
Let's go BBQ ...

flattening aggregated fields in pivot table
I'm trying to build what *should* be a simple pivot table with 2 fields and having some difficulty. The problem is that Excel wants to automatically aggregate like values. For instance, the two fields are Product Family and Part Number. There are cases where 2 different Product Families can share a Part Number (don't ask). In these few instances, The pivot table wizard automatically groups the 2 instances and only lists the Part Number once; in the second instance, it generates a blank cell where the PN should go. This wreaks havoc on one of my macros that needs every singl...

Using form to add record to table where records are limited by lis
Hi all... Bit hard to explain what I am doing here but I'll have a go :-) In Excel if I want to limit the number of records shown in rows I apply a filter for example to col1 which reduces all remaining cols. Then filter on col2 to reduce further etc etc until I have only a few records left to look at. I want to do this in access on a form BUT... I want to create a table of records that has an ID, Date stamp, a part number, qty I want this table to get its information from a form. I want the form to use a master table which includes the "part number" from above but where ...

PROPER function
Hi all - Stupid me, maybe, but I can't get this function to work. The help file says: "PROPER (text) Text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize." So the text I want to convert is in cell, say, I370. In helper cell J130 I enter =PROPER(I370) But all I get in cell J130 after hitting enter is =PROPER(I370). In other words, it doesn't seem to be acting as a formula --- it is just appearing literally. This is an inherited spreadsheet, so it's possible something ...

Form/Table structure
I want to create a form which contains details of a job to be performed by a contracting company on the site. Each company will have several jobs and many workers. I need to be able to create a new job, select a company it will be performed by and then select the workers from that company to do the job. I'm normally quite good with access but the construction of this one is baffling me. Any help on table structures and form code would be much appreciated. Thanks You need at least these 5 tables: a) Company table, with CompanyID primary key. b) Worker table, with Worke...

Pivot Table Guru Needed!!
I need some help with a pivot table. The data rows look like this: Project Year, Project Name, Project Amount I have the projects in rows and the year in columns, the amount as th data item. I'm having trouble getting the variance between years to appear on th same row as the project like this: Name 2004 2005 Variance Project 1 $500 $600 $100 Project 2 $150 $100 ($50) Total $650 $700 $50 I can get the variance on a second line, but not the same line. An help would be greatly appreciated! Stev -- Hook ----------------------------------------------------------------------- Hooks'...

=PROPER
Using Excel 2003. Have downloaded info and it was downloaded in all caps. Have tried to use the =PROPER formulat but it doesn't work. If I go to a separate cell in the spreadsheet referencing a specific cell it works. How can I change in one worksheet all caps to PROPER ? How are you using =PROPER? It's designed purpose is to be used in a different cell --- for example your information is in Cell C5 then in D5 you may put =PROPER(C5). Then in D5, you would have the information from C5 with PROPER capitalization "Snowcap" <Snowcap@discussions.microsoft.com...

Automatic print layout selection
Can you set up an option so that when you print a report, it automatically selects landscape or portrait and fits the report to the page rather than going to a 2nd line You can modify the report with Report Writer and on the Report Definition window change the Printer Orientation to Landscape. Don't forget to change security to use the modified report. You can also use Named Printers to change the printer properties to landscape if the report is one controlled by Named Printers. David Musgrave [MSFT] Original Developer of Named Printers Senior Development Consultant Escalation Eng...