Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    structure multiple attributes to a primary key

    I am trying to design a rather complicated database and unsure how to proceed. Ideally I can do this in Access, but I'm open to ideas.

    I am trying to assign multiple attributes to each primary key.

    Take the following keys below.

    350250 - Packages, FCMI, FCMI - International
    60502110 - Packages, EMI, EMI-Non NSA
    60503780 - Packages, EMI, EMI-Non NSA, GXG
    6800280 - Packages, NSA, EMI

    Say I want total package revenue, I'd want to sum all of the above. If I want total EMI revenue, I'd sum 60502110, 60503780, and 6800280. But I might then want to only sum EMI-NON NSA, in which case I'd only sum 60502110 and 60503780. Perhaps I'd want Total Packages split out by FCMI and EMI. Perhaps I want it split out FCMI, EMI, and GXG.

    The issue is that no true hierarchy exists per se.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You'll need three tables.
    One to hold the amounts.
    One to hold the keys.
    One to hold the relationships between amounts and keys (a many-to-many relationship).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2012
    Posts
    76
    How about a bit or boolean column for each attribute, e.g. something like:

    packages (key INT PK, fcmi BIT, fcmi_int BIT, emi BIT, emi_non BIT, gxg BIT, nsa BIT)

    I'm not sure I understand your requirements, so if this doesn't apply, try to expand on your explanation.

  4. #4
    Join Date
    Mar 2012
    Posts
    5
    Sorry, perhaps I was not 100% clear.. I'm new to this whole thing...

    The issue is that each primary key can belong to multiple "families". Consider the following few families:

    NSA
    Non-NSA
    PMI
    EMI
    GXG
    Packages

    Let's pretend the following primary keys exist, and belong to the following "families":

    350: NSA, EMI, Packages
    450: Non-NSA, EMI, GXG, Packages
    550, Non-NSA, EMI, Packages
    650, NSA, PMI, Packages

    If I want total package revenue, I'd sum 350, 450, 550, 650. If I want total package revenue, but split out by NSA vs. Non-NSA, I'd have two figures: the sum of the keys 350+650 (NSA Packages) and the sum of the keys 450+550 (Non-NSA Packages). If I wanted total NSA revenue, I'd see just the sum of 350+650. But if I want total NSA revenue, broken out by EMI vs PMI, I'd have two figures: 350 (NSA - EMI) and 650 (NSA - PMI). On the other hand, I may want total EMI revenue, split out by NSA vs. Non-NSA. Then I'd have two figures, sum of keys 450+550 (Non-NSA EMI) and 350 (NSA-EMI). I might want to break this out further by asking for total EMI, split out by NSA vs Non-NSA and include the sub-product of GXG breakout, in which case I'd have three totals: 350 (NSA-EMI), 450 (Non-NSA EMI GXG), and 550 (Non-NSA EMI).

    Does this make sense?

    Essentially I want to total primary keys that belong to multiple attributes, and sometimes break them up and sometimes consolidate them. But there's no "hierarchy" in the sense that ALL NSA is EMI but not all EMI is NSA... They are independent of each other.

  5. #5
    Join Date
    Feb 2012
    Posts
    76
    Normally, revenue is not a good choice for a primary key. I suggest adding a surrogate key and relegating revenue to a non-key attribute, e.g.

    TABLE (key INT PK, revenue INT, nsa BIT, nonnsa BIT, pmi BIT, ...)

    If your families are predefined and fixed, you can do it like this, if not, use blindman's suggestion.

    Your various revenue breakdowns can by achieved with suitable queries, look into the SUM aggregate function and WHERE, GROUP BY and HAVING clauses of queries.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    When answering this question keep in mind that I'm specifically using NSA and Non-NSA as examplles, but that I mean every positive/negative family pair that you will ever have. Is there a direct relationship between positive and negative family names like NSA and Non-NSA in your application? Can any package be a member of both families? Can any package be neither of neither family?

    Are the numbers you included in your sample data the revenue generated for specific packages, or are those numbers something different?

    Will you use MS-Access to query the tables? Can you imagine adding new families to your database? If you add a new family, what should be done with that family for the packages that have already been entered?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2012
    Posts
    5
    Sorry for being unclear, the primary keys are not revenue; rather, they are product codes with which revenue are associated. 350 represents a product with the listed attributes.

    Unfortunately, I don't know if positive/negative values will always work. For some attributes (such as NSA) the product is either a NSA or not an NSA. However, for others, such as EMI, the product could be EMI, PMI, GDEO, IPA, etc...

    Let's try to explain this in another way.

    350 represents a product. The product is a package product. The name of the product is EMI. 350 also is an NSA product.

    450 represents a product. The product is a package product. The name of the product is EMI. 450 is a special type of EMI that belongs to a sub-family, GXG. 450 is not an NSA product.

    550 represents a product. The product is a package product. The name of the product is EMI. 550 is not an NSA product.

    650 represents a product. The product is a package product. The name of the product is PMI. 650 is an NSA product.

    Essentially, I want to understand how to format the database so that I can query on any attribute. If I query just on NSA, it will ignore whether the product is a package product or what the name of it is. However, I also want to be able to splice apart that NSA query to delineate the names of the products composing the NSA total. That's just one example.

    I guess I don't know to make everything rollup and drilldown without duplicating. For instance, since 450 belongs to EMI and also a special subproduct, when I sum total EMI revenue - how do I get GXG to separate itself from the EMI total sometimes and other times be consolidated?

  8. #8
    Join Date
    Feb 2012
    Posts
    76
    It looks like Nate wants to classify mail service revenue. The abbreviations are USPS services: NSA = Negotiated Service Agreements, EMI = Express Mail International, GXG = Global Express Guaranteed, PMI = Priority Mail International, FCMI = First Class Mail International, IPA = International Priority Airmail. I didn't find GDEO.

    I suspect the "products" referred to are different service levels, described by the kind of deliverable (postcards, letters, large envelopes, packages), NSA vs standard service, and the shipping method: EMI, PMI, GDEO, IPA, GXG, FCMI, etc. I'm still not clear on the relationships among these methods - Nate said GXG is a kind of EMI, but there may be more.

    Am I on the right track?
    Last edited by reaanb; 03-30-12 at 13:19. Reason: Minor grammar & formatting

  9. #9
    Join Date
    Mar 2012
    Posts
    5
    Right, so the product numbers are essentially report codes used to identify various products in financial reports. Each number has a set, static list of attributes that I'd like to attribute to it.

    What I tried doing was listing out each number multiple times in 1 table, 1 row for each attribute. It looked like this:

    550 Packages
    550 EMI
    550 GXG

    The problem was that when I summed up "Packages" and selected "Group By" in Access, revenue from 550 was counted twice, once in EMI and once in GXG. My concern is that sometimes I want to roll up 550 to the EMI level and sometimes I want to break out EMI into it's components: GXG and non-GXG EMI, (just EMI). I can't figure out how to set up the relationships.

  10. #10
    Join Date
    Feb 2012
    Posts
    76
    I suggest you start with a denormalized table like Products (Key PK, Revenue, MailType, NSA, EMI).

    - Key is your product codes
    - Revenue is revenue
    - MailType is "packages" or whatever
    - NSA is Yes/No
    - EMI is one of Null, "EMI" or "GXG"

    This kind of layout should make for easy queries. How you handle the rest of the abbreviations depend on how they're related.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Forgive the length of the code sample, but I was writing SQL for speed and clarity instead of brevity:
    Code:
    CREATE TABLE Families (
       name			VARCHAR(99)
       PRIMARY KEY (name)
    )
    
    INSERT INTO Families (name)
       VALUES ('NSA'), ('Non-NSA'), ('PMI')
    ,     ('EMI'), ('GXG'), ('Packages')
    
    CREATE TABLE Products (
       id           INT
       PRIMARY KEY (id)
    )
    
    INSERT INTO Products (id)
       VALUES (350), (450), (550), (650)
       
    CREATE TABLE Product_Families (
       id			INT
       FOREIGN KEY (id)
          REFERENCES Products (id)
    ,  name			VARCHAR(99)
       FOREIGN KEY (name)
          REFERENCES Families (name)
       PRIMARY KEY (id, name)
    )
    
    INSERT INTO Product_Families (id, name)
       VALUES (350, 'NSA'), (350, 'EMI'), (350, 'Packages')
    ,     (450, 'Non-NSA'), (450, 'EMI'), (450, 'GXG'), (450, 'Packages')
    ,     (550, 'Non-NSA'), (550, 'EMI'), (550, 'Packages')
    ,     (650, 'NSA'), (650, 'PMI'), (650, 'Packages')
    
    SELECT id
       FROM Product_Families AS a
       WHERE  a.name = 'Packages'
    
    SELECT id
       FROM Products AS a
       WHERE 0 = (SELECT COUNT(*)   -- Test for non-existance
          FROM Product_Families AS b
          WHERE  b.id = a.id
             AND b.name IN ('NSA'))
    
    SELECT id
       FROM Products AS a
       WHERE 2 = (SELECT COUNT(*)   -- Test for existance of two families
          FROM Product_Families AS b
          WHERE  b.id = a.id
             AND b.name IN ('EMI', 'GXG'))
    
    SELECT id
       FROM Products AS a
       WHERE 0 = (SELECT COUNT(*)   -- Test for non-existance
             FROM Product_Families AS b
             WHERE  b.id = a.id
                AND b.name IN ('PMI'))
          AND 1 = (SELECT COUNT(*)  -- Combined with existance
             FROM Product_Families AS b
             WHERE  b.id = a.id
                AND b.name IN ('NSA'))
    
    DROP TABLE Product_Families
    DROP TABLE Products
    DROP TABLE families
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Mar 2012
    Posts
    5
    Pat-

    Thanks for the SQL. I will go through and attempt to modify as needed.

    reaanb-

    I think your organization makes sense, given what I said. I modified it a little, because I needed more fields (based on things I didn't post, which I suppose was my fault), but definitely helped. Thanks!

Posting Permissions

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