SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table structure question

    I need to show packaging - eg glass jar and cardboard box. I need to be able to show sizes and weights for postage purposes.

    I am stuck on what to use for the PK on the second table. what would you recommend. surrogate key set to auto_increment? I was hoping to use a natural key but in trying to find one, I wonder if I should have a one-to-many relationship between container & serial_number AND each possible size.

    Code MySQL:
    CREATE TABLE product_containers
    ( container_type varchar(99) NOT NULL primary key 
    ) engine = innodb default charset=utf8 collate=utf8_unicode_ci;
     
    create table container_sizes 
    ( container_type varchar(99) NOT NULL
    , container_serial_no varchar(99) NULL
    , volume tinyint(5) NULL
    , weight tinyint(5) NULL
    , length tinyint(5) NULL
    , height tinyint(5) NULL
    , breadth tinyint(5) NULL
    , unit_of_measurement varchar(12) NOT NULL
    , PRIMARY KEY  (container_type, ?????? )
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    --
    -- Constraints for dumped tables
    --
     
    --
    -- Constraints for table container_sizes
    --
    ALTER TABLE container_sizes
      ADD CONSTRAINT containerSizes_weightsAndMeasures_fk 
        FOREIGN KEY (unit_of_measurement) 
          REFERENCES weights_and_measures (symbol),
    ADD CONSTRAINT containerSizes_productContainers_fk 
        FOREIGN KEY (container_type) 
          REFERENCES product_containers(container_type);

    I need the pk of this table as an FK elsewhere.

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wonder if I should have a dimensions col for all dimensions rather than putting them in individual cols.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your one unit of measure column applies to which column? length and width? volume? or weight? because it cannot apply to all of them, shirley

    also, why are you doing this? why aren't the dimensions just attributes of each package?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    As I had 'planned' it, there would not be confusion between the several columns and the unit of weight.

    The measurements are attributes of the packet size. but across the range they can be sizes of vloume, height x width, cubic or length. whatever type, the unit would be consistent per record since we wouldn't be having 24cm x 3 inches x 5 feet.

    Your hint seems to steer me towards this place


    Code MySQL:
    create table package sizes
    ( id int not null auto_increment primary key
    , container_name
    , container size
    , unit of measurement
    ) ..

    smaple data

    | 1 | glass jar A | 50 | ml |
    | 2 | packet | 24 x 12 x 6 | cm |

    But what if I want to search through package sizes, to determine which is most suitable for the postage costs?

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    bazz, i really don't know

    you're asking questions that really belong in the realm of the subject matter expert, where the subject matter in this case is not database design but user requirements

    i don't know what you need in your tables, because i don't know what you want to record
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have such a lovely way of communicating.

    I didn't ask the correct question or explain myself properly. I'll have to work out how to explain it to myself first. got confused again.

    bazz

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    IBazz, is this for working out the postage costs for items posted in the UK and going to addresses in the UK?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    I'll have to work out how to explain it to myself first.
    this feeling is one that i am quite familiar with too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    i'm not entirely sure why product_containers exists at all - a single field table? Just for a FK? Okay...

    Anyway; You dont seem to have a natural key available -if- serial_no is NULL. Otherwise the natural key should be serial_no, no?


Bookmarks

Posting Permissions

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