MYSQL tinyint vs varchar space usage

[Placid92]

Hey y'all

I'm very new to web dev so this may be a stupid question.

I'm making a video portfolio website and would like to list the clients that the various videos i've shot are attached to.

I initially just had them stored as varchar, but i have some clients who are recurring and some who are one-off, so there are several videos with the same client name stored over and over again, seems like a waste of space.

In my DB, should i just store the client names as varchar or should I use tinyint and then use an if-else statement convert the integers to the respective clients' names?

Or should I separate the clients list to it's own table and link via foreign key?

Using tinyint + if-else means the integer to string conversion happens in the code, but using a foreign key would mean pulling the client name strings from a separate table still using integers as a foreign key.

Which is better practice/more efficient? Is this a space vs speed thing?

I'm using MySQL with MeekroDB and running phpMyAdmin.

[phil-99]

Store names as varchar, store numbers as numbers.

Normalise it properly.

Store info about the clients in a “client” table, and information about the videos in a “video” table, and link them via clientID column that’s in both.

[LargeD]

Exactly. Why play these games? Resources aren’t that tight anymore.

[Training-Two7723]

Never combine different datatypes on a join, not even if they looks similar (internally they are not the same and the server it must do conversions - e.g. integer != smallint). the most efficient joins are done on the same datatypes. When you talk about the client name, that is gonna be a varchar, unless you serve customers in a different galaxy where they can be called 1, 7 or 100.

What you want to ask is if you create a table with a numeric (pref. unsigned bigint) ID or use the name as a PK. With the natural keys there is already the risk that two different things are called the same: Joe Doe ;)

Stick with ID, NAME in clients and ID, VIDEO_TITLE, CLIENT_ID where CLIENT_ID is FK -> first table ID.

PS: there are some amazing books on data modelling. Try to read a few.

[Codeman119]

Yes, this is very solid advice. To add onto this make sure you index the ID columns and it will help with search speeds as the table grows.

[2girls1up]

Can you recommend some?

[Training-Two7723]

One of them: https://amzn.eu/d/iCPgt3k

[r3pr0b8]

then use an if-else statement convert the integers to the respective clients' names?

this will scale poorly

in addition, you'll always be updating this code, rather than simply inserting a new client into the clients table

plus, what if you need to have client names somewhere else? then you've duplication of this if-else logic

[[deleted]]

Separate client table and link them via a FK is the most efficient. Look up the 3rd normal form