1, 2, or 3 tables? (AXP 10.2627.2625)

Topic

New Reply

Designing for this hypothetical process:

A client is sending me monthly data from 2 departments in 2 separate emails. Let’s assume i can read the emails and import the data from each dept into their own tables (T1 and T2). The common elements in each table are clientID, month, year.

A report for the month requires calculating results based on data from both tables.

Now there is a chance to get all the data in a single email. This leads me to think that i could combine these 2 tables into one master table (T3) using various update and append queries, but this is getting to be complicated. Taking that complication as “a sign”, I am wondering if i should rather split the 1 big email into two parts and carry on as before.

Reply | Quote

Replies

Peter,
If in the long run combining the data into a single table will save time, I would go ahead and do it. However, you must consider how similar the table layouts are of T1 and T2. If they are very similar, it seems best to combine them. If ClientID, month and year are the only consistent elements, then maybe it is best to keep them separate.
Dashiell

Reply | Quote

At this point ‘saving time’ is of secondary import. What I am trying for is clean design.

The data are heterogeneous: email 1 contains dollars and people counts, email 2 contains dollars and part counts. These are combined in multiple calculations to recommend inventory policy.

The reason for 2 emails instead of 1? Politics. As usual

Reply | Quote

As an alternative to Dashiell’s suggestion, if there are structural differences betwwen the departments (and that doesn’t make too much sense to me), you could put the common elements (clientID, month, year, etc) into a single master table, along with fields to identify the department AND a primary key, preferably an autonumber. Then you could have another table or tables in one-to-one relationship to the main table, that uses the PK from the main table as their PK as well. That would allow you to have widget data stored in the widgets table for department 1 and gadget data stored in the gadgets table for department 2. You could immediately link widget and gadget information back to the parent records in the main table for reporting and analysis.

Reply | Quote

Notes re table contents in my reply to dashiell.

I am thinking along these lines:
Let R be the Receiver table, and S1 and S2 the incoming datasets.
* use the month, year, & name as a key, guaranteeing uniqueness in S1 and S2
* append query S1 to R
* delete all S1 rows
* update query S2 to R
* select common rows of S2 and R
* delete common rows from S2

It doesnt seem so complicated having written it out -but am I overlooking something here?

Reply | Quote

Sorry, but I’m not sure what S1 and S2 actually represent. I thought you said the data came in with different structures, so do S1 and S2 represent not only the data from different sources but also *different data*? Are they permanent tables? If so, why are you deleting records from S1 but only deleting common records from S2? What are you going to do with the rest of the rows in S2? And what does name represent and is it unique in any sense?

What about having an R table with say 8 fields: RID, month, year, department, counttype, dollars, name and countvalue?

Assume that S1 has the structure month, year, dollars, name and peoplecount and that S2 has the structure month, year, dollars, name and partscount. Append S1 to R with “S1” going to department, peoplecount going to countvalue and ‘people’ going to counttype. Append S2 to R with “S2” going to department, partscount going to countvalue and ‘parts’ going to counttype. Now you could create a unique key on department, month, year, name and counttype (unless you can make do with department, month, year and name).

Why would you need to delete anything?

Reply | Quote

>> Sorry, but I’m not sure what S1 and S2 actually represent. I thought you said the data came in with different structures, so do S1 and S2 represent not only the data from different sources but also *different data*?

Right, different data. BUT, both are used as Sources for calculations that relate to a single month. S1 is the money data; the guy who is sending the S2 component is not allowed to look at S1.

>> Are they permanent tables?
Yes, in the sense that they will continue to receive data each month.

>>If so, why are you deleting records from S1 but only deleting common records from S2?
My thought is that I can use name&year&month as some kind of ‘manufactured’ key that identifies these data coming in from the client named ‘name’. There will be several clients sending us data in the same format.

The complete calculation cannot be done without both parts of the data, S1 and S2. If i append data from S1 to R, that will be ‘new’ data. It is not certain that matching records from S2 will have arrived yet, OR, it may be that the S2 data have arrived but the S1 has not. Both S1 & S2 are required for a valid calculation. I decided to append S1 data and update using S2 data as a straightforward way to keep track of what has arrived. The only S2 data being deleted corresponds to that which has already been added to R. i am beginning to think that i am better off with 2 tables, and doing the calculations on the records that are joined for that month.

>>What are you going to do with the rest of the rows in S2?

Eventually the appropriate data from S1 will arrive, be appended to R. At that point, the S2 data are updated, and the month/year record is complete.

>> And what does name represent and is it unique in any sense?

name is the client ‘name’, and is unique.

>>What about having an R table with say 8 fields: RID, month, year, department, counttype, dollars, name and countvalue?

>>Assume that S1 has the structure month, year, dollars, name and peoplecount and that S2 has the structure month, year, dollars, name and partscount. Append S1 to R with “S1” going to department, peoplecount going to countvalue and ‘people’ going to counttype. Append S2 to R with “S2” going to department, partscount going to countvalue and ‘parts’ going to counttype. Now you could create a unique key on department, month, year, name and counttype (unless you can make do with department, month, year and name).

The dept is not really important to the classification of the data, so i dont see how this would be an improvement.

>> Why would you need to delete anything?

Once the data is in R, i dont need it anywhere else, so why would i want to keep it? OTOH, these questions move me in the direction dashiell suggested in his reply.

Thanks Charlotte – Great questions, as always! I will continue to mull this over, altho the sense i get is that what i am suggesting doing is unnecessary.

Reply | Quote

I don’t understand why you have 2 tables in the first place? If the 2 input files have identical structure, then I believe it should be in a single table.

Reply | Quote

well, there are two tables because I’m NEW at this .

The whole deal comes out of a vast collection of spreadsheets that are attempting to duplicate database functionality thru code. The conglomeration has at last become too cumbersome for the client. (no, I am not the client )

Reply | Quote