« first day (2923 days earlier)      last day (223 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.
 
12:45 AM
@billynoah For such a small table, it won't be any difference really, in my opinion.
 
thanks - at what size do you start to consider datatype on a column like that to matter?
 
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.
 
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
 
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.
 
thanks
 
12:54 AM
Which DBMS do you use?
 
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
 
@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.
 
good idea
 
and you still use only 1 or 2 bytes
 
is CHAR(1) close to INT in terms of efficiency?
TINYINT i should say
 
12:59 AM
Yes, similar.
Text comparisons may be somewhat slower (not sure for MySQL) but not something you'd notice
 
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
 
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.
 
i've never quite understood constraints.. i guess it's another point of redundancy. all of my validation happens in php
 
@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.
 
i guess the constraints show you the bugs a lot faster than noticing 2 months later that you have a faulty insert
 
1:04 AM
exactly
 
something new to learn about!
thanks for your input.. my first visit to chat and it was a positive experience
 
@billynoah Welcome. I see you have posted questions so you are not new to the site.
 
Yeah I've been on SO for years - finally broke 10k rep a month ago. Don't frequent dba as much
 
 
9 hours later…
9:41 AM
@billynoah here's a previous answer on this exact topic
8
A: What are the best practices regarding lookup tables in relational databases?

Michael GreenThere 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…
11:13 AM
@JackDouglas morning, can I execute a block of code (Mariadb) on fiddle?
 
 
2 hours later…
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…
2:36 PM
@AndriyM I think a @variable cannot be shared between batches. And it doesn't allow multi-lines.
 
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?
26
A: Executing multiple SQL queries in one statement with PHP

HusniPass 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
 
@McNets Actually it looks like you can share a variable between batches in MySQL/MariaDB: dbfiddle.uk/…
 
@AndriyM thanks :)
 
@JackDouglas no, it isn't. thanks
@AndriyM thanks !
 
@McNets do you mean it is better not to turn CLIENT_MULTI_STATEMENTS on or are you saying it isn't on?
 
@JackDouglas I think it is not necessary to activate it.
 
3:41 PM
ok, cool
 
 
5 hours later…
8:21 PM
Lol "Prof. Dr."
kids these days.
ONE SECOND, BRB.
HOW DID I DO?
 
9:05 PM
Professional drop kick?
 
9:49 PM
hahaha
The Honorable Sri Sri Professor Dr. Evan "The Great" Carroll GBE.
 

« first day (2923 days earlier)      last day (223 days later) »