Require guidance over sql table architecture

Question

I'm fairly new to SQL and I'm having a hard time coming up with a good plan for tables etc to my problem. I'm attempting to create a program that makes use of Sqlite to make it easier for myself to discern postage.

I have an item table that has a few item specifics, such as weight. My problem lies with the rest of the tables that I was planning on creating. I essentially want the program I am making to decide what the best postage service is, based on the item weight, package weight and how many items can fit into the package.

I'm not sure how to go about implementing the postal service and packaging. The postal service table would have information such as first/second class and max weight. The packaging table would essentially be like the items table, in the sense that it too has its own weight (as total weight of the package needs to be calculated), however, each package may fit a different number of items in it from the items table.

Would I essentially have to implement in a way where I cross-referenced the item in the packaging table so that there would be a column stating how much of each item fits in each package? I wouldn't then be sure how to reference the service table to get the max weight from the service.

I thought implementing it like so would be an okay attempt:

Items

id INTEGER, title TEXT, sku TEXT, weight DOUBLE

Packaging

id INTEGER, stock_id INTEGER, title TEXT, weight DOUBLE, max_qty INTEGER, FOREIGN KEY(stock_id) REFERENCES items(id)

Services

id INTEGER, class_type TEXT, max_weight DOUBLE, cost DOUBLE

However, the above means that for every item I was to create, I'd have to create a new row in the packages table for each package the item could use.

I'd just like to know what the best way to go about this would be and possibly some advice to go a long with it if possible.

Answer 1

Would I essentially have to implement in a way where I cross-referenced the item in the packaging table so that there would be a column stating how much of each item fits in each package?

Yes, but instead of putting the cross-reference in the packaging table, use a junction table:

Proposed database structure in crow's-foot notation

The junction table (Item Packaging) has one row for each relationship between an item and a package.

For example, say you have three items:

1. Whatsit (ID 1)
2. Thingamabob (ID 2)
3. Doohickey (ID 3)

And you have two package types:

1. Small Box (ID 1)
2. Large Box (ID 2)

Also say that this is how many items can fit into each packaging type:

• 3 Whatsits in a Small Box
• 6 Whatsits in a Large Box
• 1 Thingamabob in a Small Box
• 3 Thingamabobs in a Large Box
• 1 Doohickey in a Large Box

In that case, you would insert these rows into Item Packaging:

---------------------------------------------------
Item ID Packaging ID Item Quantity
---------------------------------------------------
1 1 3
1 2 6
2 1 1
2 2 3
3 2 1

Your program would include the junction table in its queries to determine how much of the specified item could fit into the specified package and calculate the total weight. Then you could compare the calculated weight to the maximum weight of each postal service.

For instance, to get the total weight of each package holding as many Whatsits as it's capable of:

SELECT (item.weight * item_packaging.item_quantity + packaging.weight) AS total_weight FROM item JOIN item ON item_packaging.item_id = item.id JOIN packaging ON item_packaging.packaging_id = packaging.id WHERE item_packaging.item_id = 1;

• BoyUnderTheMoon: Thank you very much for such an in-depth answer, I am very grateful.