Jump to content
Sign in to follow this  
mtpaper

One Table or Two?

Recommended Posts

What are your 'best practice' recommendations for this?

In the past, I have one table for entering income and expense amounts, with sub-totals by category (ie, each record has fields for id, date, category, amount). The expenses were entered as negatives.

I'm in the process of building a new database, with a similar situation (income types, less various adjustments = net income) and I'm second-guessing how it should be structured.

Do you recommend:

a. one table with income and adjustments (as I've done before)

b. one table with separate fields for income and adjustments (I don't think so)

c. one table for income and another table for adjustments

d. other?

I feel as if it should be two tables, but I'm having trouble wrapping my head around it that way. Because I think of both tables as being the 'body' or detail of a report, and can't figure out how they'd co-habitat.

Thank you -

Marion

Share this post


Link to post
Share on other sites

There are great inspiration to get in in this:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

...so a)

--sd

Share this post


Link to post
Share on other sites

It just so happens I'm in the middle of developing a solution that had the same problem - but in my instance there are around 8 tables to consider...

Basically my client runs a business that he operates as multiple departments - these are the 8 tables. However, for the purposes of reporting, particularly quotations, all the data will need to exist in the same table. Reason? When FM reports data and you use the BODY layout part then that data must come from the TABLE OCCURRENCE that the layout is based upon.

My solution was to create the fields necessary for each department into a single TABLE, but then I used different TABLE OCCURRENCES to represent the departments.

This is, in effect, a series of SELF JOIN relationships, and I admit it is not 'normalised' as it would be with separate tables, but I had to achieve the goal of the reporting requirements.

Share this post


Link to post
Share on other sites

.... all the data will need to exist in the same table. Reason? When FM reports data and you use the BODY layout part then that data must come from the TABLE OCCURRENCE that the layout is based upon.

That's exactly what I thought.... But my husband, who works with Approach, says that I should be able to join the tables (let's say by date) and have them all appear in the body of my report.

I don't think it's possible that way - for FM or any db application - because you'll only get the 1st record from each table.....

I think I will pursue one table for the records, and see if I can enter all the data as positive numbers, but create formulas to handle the subtracting of sub-totals for specific categories....

Thank you -

Share this post


Link to post
Share on other sites

Try to read this thread with (yikes) all the brances and nestings:

http://fmforums.com/forum/showtopic.php?tid/196508/post/296095/#296095

--sd

Share this post


Link to post
Share on other sites

If (as it seems) all transaction types require the same fields - what possible advantage would be gained by keeping them in separate tables?

Share this post


Link to post
Share on other sites

I agree - but I'm trying to reconcile with how I think I should set up my database and how my husband thinks I should set up my database!

Share this post


Link to post
Share on other sites

That seems to be more of a relationship issue...

So it looks like there are no advantages, and there is at least one big disadvantage. Tough choice. BTW, in an SQL database you can do a UNION join, which effectively produces a combined report from two tables. However, that doesn't mean that a database that splits transactions of the same "account" into separate tables by types is correctly designed. The concept of 'entity' remains the same no matter which RDBMS you use.

Share this post


Link to post
Share on other sites

@IdealData

Help has very little to say about Table Occurrences....

I interpreted it to mean that I'm placing the same detail table on my relationship window twice. But, when I tried to do that, the linking field is no longer available....

Can you please elaborate, or post a mini-example of how to do this for me to mimic?

Thank you -

Marion

Share this post


Link to post
Share on other sites

Help has very little to say about Table Occurrences....

Perhaps, but this does:

http://www.fmp.it/download/files/FM7_key_concepts.pdf

--sd

Share this post


Link to post
Share on other sites

Ok - the article about Table Occurrences was helpful, thank you. If I understand correctly, it's basically an Alias table, so that you can use the data twice. But I'm not sure how that's helpful for me - or perhaps, it just isn't the right solution.

Let's say I have a table of records:

id

category (values of: Advertising, labor, rent, etc)

amount

I would like a list report, with advertising in one column, labor in another column... and perhaps I don't even want rent to show.

I know I can export to Excel and have it via a pivot.

I know I can create empty records, and create a faux pivot in Filemaker

Is there a way to achieve this with Table Occurrences? Or some other technique?

Thank you -

Marion

EDIT: In Excel, I have access to a SUMIF Formula which adds the cells specified by a given criteria. Does Filemaker have an equivalent?

If so, I could place the field on my list layout multiple times... one time specifying the sum of advertising, and then the sum of labor.

and place the fields in the same horizontal row of my layout.

Edited by Guest

Share this post


Link to post
Share on other sites

I have access to a SUMIF Formula which adds the cells specified by a given criteria. Does Filemaker have an equivalent?

You would be hard stressed to find a database tool which doesn't provide a subsummary function - and neither does filemaker fail in that respect:

...but it carries evidence that my previous linking didn't make much sense. Edoshins Crosstab reporting, could be changed to usher figures into the appropriate columns. A simple case( statement can turn account number/description into a repetitionnumber pretty simple ... however can it pay off to make this calc as snappy as possible:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000087

--sd

Share this post


Link to post
Share on other sites

Hello -

This is semi-related to the above thread, but expands the original question now that that has been resolved in my mind.

I am re-building my database for tracking information related to performances (ie, events).

As described earlier in the thread, some of the tidbits of info have date and monetary information, such as:

- EventID, date, time, amount, category

- an eg would be: Event03, May 5, 2009, 2p, 5000, Performances

Some of the tidbits of info is focused on date and a comment:

- Event ID, Date, time, category, comment

- eg, Event03, May 3, 2009, 1pm, Production, 1st rehearsal

- eg, Event03, May 5, 200, 2pm, Performances, 1st public performance

If I keep my non-money tidbits separate from my money-tidbits, then I get into trouble when I'm trying to make body-sections in reports. And I wind up with double data entry (ugh) for something like the May 5th performance, which needs to be recorded in both tables.

I'm tempted to make one big tidbit table, like this: EventID, date, time, amount, category, comment

but some records will not have an amount, and some records will not have a comment.

Is that sloppy database design?

What do you think or advise?

I omitted other fields for simplicity of conversation (such as currency, exchange rate, amount in US$, etc).

Thank you -

Marion

Edited by Guest
cleanup

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.