Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    well, there are two tables because I'm NEW at this <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    The whole deal comes out of a vast collection of spreadsheets that are attempting to duplicate database functionality thru code. <img src=/S/puke.gif border=0 alt=puke width=60 height=15> The conglomeration has at last become too cumbersome for the client. (no, I am not the client <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    >> 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •