When to split to another DB table

Question

My question is about databases and when to split into other tables. I am making an inventory tracker for multiple platforms. For example, I have an Item resource, and I am going to connect to different platforms like Foo and Bar that have Items. There are shared properties like name, quantity, sku, barcode, but the platforms have different properties specific to them. They'll have their own unique ID, Foo might have tags, and Bar might not but have a vendor tag. Right now what I've done is something like this:

Item
- id
- name
- sku
- barcode
... bunch of others
- foo_id
- foo_tags
- foo_product_type

But if I integrate with Bar platform, should I add fields to the Item resource like bar_id, bar_vendor, bar_other_properties? Or should it be created as another table, and Item would have a foreign key to that table? When should I split these platform specific properties into another table? Performance-wise, is the extra join going to make things slower? For example, if I'm updating all these items for a user, I'd be joining the User, Shop, Item, and now potentially PlatformSpecificItemData, and I do not know if there is a point where there are too many tables to join and impacts performance.

Answer 1

This can be difficult sometimes to be honest. I use database a lot, but rarely are they really that huge, not big as in lines, but big as in fields. Normally 5 or less tables.

But from my experience the more you can break up the tables the easier it is to expand a database and edit or maintain it later on. This might seem dumb, but imagine if all of those are in one table, it makes it super easy to use, but now what if you change tags to something else where you have 2 fields instead of 1 and so on. Or what if you have a system that still needs the old version of tags, but the new system doesn't use it, you can't just delete them now can you? Splitting it up allows you much more flexibility.

I would suggest the required information is in the main table and the rest is split up. This can slow down queries a little bit and be a bit more difficult to create proper queries if you are not as experienced, but well worth it when you start changing things in the long run.

items
    - id
    - name
    - sku
    - barcode
items_physical_properties
    - id
    - items_id
    - width
    - height
    - weight
    - quantity
    - color
items_digital_properties
    - id
    - items_id
    - tags
    - image
items_information
    - id
    - items_id
    - manufacturer
    - manufactured_date
    - vendor
    - company
    - created_date
items_pricing
    - id
    - items_id
    - sell_price
    - cost_price
items_sales
    - id
    - items_id
    - sale_price
    - start_date
    - end_date
    - amount_sold

I would split it up something like this, the reason is, let's say you create a third party api or idk a cash register it makes it a lot easier to limit what you give to them. This also makes it easier to limit what queries you make, lets use the cash register as an example. They don't need to know the manufactured_date, but maybe a guy in the backroom using a hand scanner does.

// my mysql is a bit rusty, but here is an example

// cash register
SELECT 
    items.id, items.name, 
    items_prices.sell_price,
    items_sales.sale_price
FROM items
JOIN items_prices ON items.id=items_prices.items_id
JOIN items_sales ON items.id=items_sales.items_id

The hand scanner guy needs different information, while there are other ways of controlling this some databases let you control it buy the user who accesses them. It makes it much easier when working with third parties, or just personally mixing and matching queries.

You could even go more in-depth, but there is a point where it just creates more work for what little you get out of it. Not sure if this helps, but it is my experience using databases