The Heap™ – Consultancy ©®

« first day (2923 days earlier) ← previous day next day → last day (1928 days later) »

12:27 AM

is it appropriate to ask for some opinion based guidance here — probably off topic for main site?

I'm building a table for organizations and each will have a "type" - which will be one of 2 or 3 things. is it ill advised to simply store them as strings in a varchar column, or should i stick with the standard of storing them as INT and keeping a separate table to decode the INT to a human readable string. bear in mind this table will probably max out around 1000 rows.

yper-crazyhat-cubeᵀᴹ

12:45 AM

@billynoah For such a small table, it won't be any difference really, in my opinion.

billynoah

thanks - at what size do you start to consider datatype on a column like that to matter?

yper-crazyhat-cubeᵀᴹ

It all depends on the use of the table really.

This looks like a static table or a very slowly growing one.

If it was a table that you'd expect to keep growing, then I'd consider such optimizations, as the column sizes make a difference, especially when you want to add indexes.

billynoah

slowly growing.. organization will be treated differently depending on type so I'll definitely be indexing this column and needing data to be one of a few values. i considered enum but read enough warnings about issues with it to shy away from it

yper-crazyhat-cubeᵀᴹ

Narrow columns -> smaller indexes.

There are also things to consider.

Say you need this type to be either CRA, TRUCK or BUS.

You can surely add a CHECK constraint, like

CHECK type IN ('CAR', 'BUS', TRUCK')

but when you'll want to add a 4th type, you'll have to delete and recreate the constraint.

Not a very serious problem with small table but still, a bit more trouble than with int and a reference table, where you'll just insert a 4th row.

billynoah

thanks

yper-crazyhat-cubeᵀᴹ

12:54 AM

Which DBMS do you use?

billynoah

i usually regret being lazy

mysql

the lookup table is probably best - the only thing I hate is when manually viewing the data I always need to check the lookup table to remind myself which is which

once it's 4 or 5 values i end up needing a join to make it human readable

yper-crazyhat-cubeᵀᴹ

@billynoah which doesn't have CHECK constraints

unless you are in MariaDB

@billynoah Yeah, lookup tables are good.

FOr small number of types, a variation would be a lookup table with CHAR(1) or CHAR(2) pk

B Bus
C Car
T Truck

The inspection is easier that way.

billynoah

good idea

yper-crazyhat-cubeᵀᴹ

and you still use only 1 or 2 bytes

billynoah

is CHAR(1) close to INT in terms of efficiency?

TINYINT i should say

yper-crazyhat-cubeᵀᴹ

12:59 AM

Yes, similar.

Text comparisons may be somewhat slower (not sure for MySQL) but not something you'd notice

billynoah

i don't think i would even need a lookup table if I use CHAR - there's only one place in the UI I'd ever need to show a decoded value and it can be hard coded as an array

yper-crazyhat-cubeᵀᴹ

The lookup is needed if you want to enforce constraints in the database.

Otherwise, you want only types C, B, T and by an error you'll get a row with type G.

If you have the lookup and a foreign key, the db won't allow it.

billynoah

i've never quite understood constraints.. i guess it's another point of redundancy. all of my validation happens in php

yper-crazyhat-cubeᵀᴹ

@billynoah Yes, sure. You can do that. But you are at the mercy of the application and any bug it may have.

All the applications really that may use the database, now and in the future.

billynoah

i guess the constraints show you the bugs a lot faster than noticing 2 months later that you have a faulty insert

yper-crazyhat-cubeᵀᴹ

1:04 AM

exactly

billynoah

something new to learn about!

thanks for your input.. my first visit to chat and it was a positive experience

yper-crazyhat-cubeᵀᴹ

@billynoah Welcome. I see you have posted questions so you are not new to the site.

billynoah

Yeah I've been on SO for years - finally broke 10k rep a month ago. Don't frequent dba as much

9 hours later…

Michael Green

9:41 AM

@billynoah here's a previous answer on this exact topic

A: What are the best practices regarding lookup tables in relational databases?

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full value and is unique. For your given example it may be Idn: 1 Name: Democrats Code: D (or DEM...

@George.Palacios Wait wut! A little bit of kleptomania is OK? #IveBeenDoingItWrong :(

1 hour later…

McNets

11:13 AM

@JackDouglas morning, can I execute a block of code (Mariadb) on fiddle?

2 hours later…

Andriy M

1:10 PM

@McNets I think DELIMITER is a client-side instruction. It effectively specifies a batch delimiter, for the client to know when to stop reading the user input and pass what has already been read to the server.

At dbfiddle.uk you don't need to specify a batch delimiter, because each text field will be treated as a separate batch

1 hour later…

McNets

2:36 PM

@AndriyM I think a @variable cannot be shared between batches. And it doesn't allow multi-lines.

Jack Douglas

3:28 PM

@McNets there is the option to use CLIENT_MULTI_STATEMENTS in MySQL (and presumably also in MariaDB) — do you think it would make sense to turn it on?

A: Executing multiple SQL queries in one statement with PHP

Pass 65536 to mysql_connect as 5th parameter. Example: $conn = mysql_connect('localhost','username','password', true, 65536 /* here! */) or die("cannot connect"); mysql_select_db('database_name') or die("cannot use database"); mysql_query(" INSERT INTO table1 (field1,field2) VALUES(1,2...

db<>fiddle's design philosophy is against parsing the SQL you enter at all (the one exception being the 'split batch' function, but that is really peripheral to the main working of the site). In other words, this behaviour is inherited from the database itself, or the drivers used to access the DB. There is either something about MySQL or something about the MySQL driver for PHP causing this. It may well be related to this: stackoverflow.com/a/13982705/6854914 but it looks like there are pros and cons. — Jack Douglas ♦ Sep 29 '18 at 11:14

Andriy M

@McNets Actually it looks like you can share a variable between batches in MySQL/MariaDB: dbfiddle.uk/…

Jack Douglas

@AndriyM thanks :)

McNets

@JackDouglas no, it isn't. thanks

@AndriyM thanks !

Jack Douglas

@McNets do you mean it is better not to turn CLIENT_MULTI_STATEMENTS on or are you saying it isn't on?

McNets

@JackDouglas I think it is not necessary to activate it.

Jack Douglas