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.


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

I wonder if I should have a dimensions col for all dimensions rather than putting them in individual cols.

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 :wink:

also, why are you doing this? why aren’t the dimensions just attributes of each package?

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


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

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

You have such a lovely way of communicating. :cool:

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

IBazz, is this for working out the postage costs for items posted in the UK and going to addresses in the UK?

this feeling is one that i am quite familiar with too :smiley:

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?