SOLVED

Setting up a Table. One table for all columns, or each column a table? Does it make a difference

%3CLINGO-SUB%20id%3D%22lingo-sub-2711579%22%20slang%3D%22en-US%22%3ESetting%20up%20a%20Table.%20One%20table%20for%20all%20columns%2C%20or%20each%20column%20a%20table%3F%20Does%20it%20make%20a%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2711579%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20seems%20to%20be%20a%20different%20response%20when%20setting%20up%20a%20table.%20E.g.%20Data%20in%20four%20columns-%20should%20this%20be%20one%20table%2C%20OR%2C%20should%20each%20column%20be%20its%20own%20table%3F%20Does%20it%20make%20a%20difference%20when%20using%20formulas%20on%20the%20individual%20columns%3F%26nbsp%3B%20Different%20formulae%20of%20course%20have%20different%20data%26nbsp%3B%20needs%2C%20so%20is%20there%20a%20%22better%20solution%22%20that%20can%20cover%20most%20contingencies%3F%26nbsp%3B%20Thanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2711579%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2711622%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20a%20Table.%20One%20table%20for%20all%20columns%2C%20or%20each%20column%20a%20table%3F%20Does%20it%20make%20a%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2711622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126971%22%20target%3D%22_blank%22%3E%40PhilipLibman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20the%20data%20in%20one%20row%20%22belong%22%20together%2C%20for%20example%20first%20name%2C%20last%20name%2C%20date%20of%20birth%20and%20place%20of%20birth%20of%20a%20person%3F%20If%20so%2C%20you%20should%20create%20one%20table%20-%20you%20want%20the%20data%20to%20stay%20together%20in%20the%20same%20row.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20are%20the%20data%20not%20related%20to%20each%20other%2C%20and%20could%20one%20column%20have%20more%20or%20fewer%20rows%20than%20another%20column%3F%20In%20that%20case%2C%20it's%20better%20to%20create%20separate%20tables%2C%20so%20that%20each%20can%20be%20manipulated%20independently%20from%20the%20others.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2711767%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20a%20Table.%20One%20table%20for%20all%20columns%2C%20or%20each%20column%20a%20table%3F%20Does%20it%20make%20a%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2711767%22%20slang%3D%22en-US%22%3E%40HansVogelaar%3CBR%20%2F%3EThanks%20for%20the%20quick%20reply.%20In%20this%20case%2C%20the%20formulae%20will%20query%20separate%20columns%20as%20needed%2C%20so%20better%20to%20create%20separate%20tables.%3CBR%20%2F%3EMuch%20appreciated!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2721218%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20a%20Table.%20One%20table%20for%20all%20columns%2C%20or%20each%20column%20a%20table%3F%20Does%20it%20make%20a%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721218%22%20slang%3D%22en-US%22%3Eif%20the%20data%20in%20each%20column%20is%20related%20to%20each%20other%20set%20up%20all%20columns%20into%20one%20table%3CBR%20%2F%3Eif%20the%20data%20in%20each%20column%20are%20not%20related%20then%20that's%20your%20prerogative%20whether%20you%20want%20to%20create%20the%20former%20or%20the%20latter%20also%20dependent%20on%20what%20type%20of%20table%20they%20are%20they%20are%20either%20a%20transaction%20table%20(which%20applies%20to%20the%20former)%20or%20a%20dim%20table%20(a%20lookup%20table)%20which%20in%20a%20way%20is%20usually%20applicable%20to%20the%20former%20and%20not%20the%20latter%20otherwise%20why%20the%20heck%20would%20you%20create%20several%20tables%20for%20dataset%20that%20are%20related%20to%20one%20another%2C%20the%20answer%20to%20that%20is%20to%20normalize%20the%20dataset%20so%20there%20wont%20be%20a%20lot%20of%20redundancy%20for%20example%20name01%20and%20name02%20lives%20in%20the%20same%20neighborhood%20so%20if%20your%20dataset%20requires%20their%20full%20addresses%20there%20would%20be%20a%20redundancy%20of%20city%2C%20state%2C%20and%20zip%20code%20in%20your%20table%2C%20whereas%20if%20you%20create%20a%20table%20of%20zip%20addresses%20comprising%20of%20city%2C%20state%2C%20zip%20all%20you%20have%20to%20enter%20after%20the%20street%20address%20in%20your%20first%20table%20is%20the%20zip%20code%20so%20when%20you%20finally%20need%20that%20info%20in%20some%20report%20the%20zip%20code%20can%20be%20used%20to%20query%20the%20city%20and%20the%20state%20thereby%20saving%20you%20the%20cost%20of%20gigabytes%20of%20disk%20space%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2735163%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20a%20Table.%20One%20table%20for%20all%20columns%2C%20or%20each%20column%20a%20table%3F%20Does%20it%20make%20a%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2735163%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20detailed%20response.%20Much%20appreciated.%3C%2FLINGO-BODY%3E
Occasional Contributor

There seems to be a different response when setting up a table. E.g. Data in four columns- should this be one table, OR, should each column be its own table? Does it make a difference when using formulas on the individual columns?  Different formulae of course have different data  needs, so is there a "better solution" that can cover most contingencies?  Thanks. 

4 Replies
best response confirmed by PhilipLibman (Occasional Contributor)
Solution

@PhilipLibman 

Do the data in one row "belong" together, for example first name, last name, date of birth and place of birth of a person? If so, you should create one table - you want the data to stay together in the same row.

 

Or are the data not related to each other, and could one column have more or fewer rows than another column? In that case, it's better to create separate tables, so that each can be manipulated independently from the others.

@HansVogelaar
Thanks for the quick reply. In this case, the formulae will query separate columns as needed, so better to create separate tables.
Much appreciated!
if the data in each column is related to each other set up all columns into one table
if the data in each column are not related then that's your prerogative whether you want to create the former or the latter also dependent on what type of table they are they are either a transaction table (which applies to the former) or a dim table (a lookup table) which in a way is usually applicable to the former and not the latter otherwise why the heck would you create several tables for dataset that are related to one another, the answer to that is to normalize the dataset so there wont be a lot of redundancy for example name01 and name02 lives in the same neighborhood so if your dataset requires their full addresses there would be a redundancy of city, state, and zip code in your table, whereas if you create a table of zip addresses comprising of city, state, zip all you have to enter after the street address in your first table is the zip code so when you finally need that info in some report the zip code can be used to query the city and the state thereby saving you the cost of gigabytes of disk space
Thanks for the detailed response. Much appreciated.