Morons making database schemas

Message Bookmarked

Bookmark Removed

THERE IS A "DATETIME" DATATYPE FOR A REASON - VARCHAR(20) DATE SUBMITTED = FUQ U

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:34 (twenty-one years ago)

I read this as "mormons" and thought "wow".

― Andrew (enneff), Wednesday, 29 September 2004 15:35 (twenty-one years ago)

the datetime datatype is shite

― ken c (ken c), Wednesday, 29 September 2004 15:36 (twenty-one years ago)

it is better than a VARCHAR

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:37 (twenty-one years ago)

Agreed, DATETIME sucks the balls. INT(11) all the way.

― Andrew (enneff), Wednesday, 29 September 2004 15:38 (twenty-one years ago)

(DATETIME is only mildly better than a VARCHAR imo)

― Andrew (enneff), Wednesday, 29 September 2004 15:38 (twenty-one years ago)

What's a good book on constructing databaes in general and PostgreSQL in particular.

― Ed (dali), Wednesday, 29 September 2004 15:40 (twenty-one years ago)

Add punctuation and speeling where you please

― Ed (dali), Wednesday, 29 September 2004 15:40 (twenty-one years ago)

What's a good book? On, constructing... databaes!! In general, and, PostgreSQL. In particular!!!!!!!!

― Andrew (enneff), Wednesday, 29 September 2004 15:41 (twenty-one years ago)

what's so bad about datetime? (not a DB person)

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:42 (twenty-one years ago)

Poxy fules! Dattime rockith!

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 15:42 (twenty-one years ago)

Datetime rockith!

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 15:42 (twenty-one years ago)

I misread this as "Momus making database schemes" - truly an insane venture if ever there was one.

― DV (dirtyvicar), Wednesday, 29 September 2004 15:44 (twenty-one years ago)

otherwise you basically gotta toss in a to_date in all your to_char.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 15:44 (twenty-one years ago)

Aside from crazy punctution, is there an answer to my question?

― Ed (dali), Wednesday, 29 September 2004 15:45 (twenty-one years ago)

DATETIME is fucking bullshit because all you need is the number of seconds since Midnight, January 1st 1970. It's by far the simplest way of doing anything ever!

― Andrew (enneff), Wednesday, 29 September 2004 15:45 (twenty-one years ago)

Ed, I don't read books on programming. I just do it. I guess I'm just hardcore that way, ya know?

― Andrew (enneff), Wednesday, 29 September 2004 15:45 (twenty-one years ago)

unless your trying to read it.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 15:46 (twenty-one years ago)

How did you learn DB/SQL in the first place? Osmosis?

― Ed (dali), Wednesday, 29 September 2004 15:46 (twenty-one years ago)

unless your trying to read it.

You've never seen my code. It is truly a thing of beauty.

― Andrew (enneff), Wednesday, 29 September 2004 15:47 (twenty-one years ago)

My code is like a Zen Koan.

― Andrew (enneff), Wednesday, 29 September 2004 15:47 (twenty-one years ago)

listen: storing date as a string = dud... i wish they'd do it so it would sort lexigrahpically-- (i think thats what i mean)

"2004/09/23 13:00:00" = ok I suppose
"9/23/2004 1:00:00 PM" = dud

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:47 (twenty-one years ago)

and it's fucking impossible to render datetime into a sensible display format

― ken c (ken c), Wednesday, 29 September 2004 15:48 (twenty-one years ago)

Look FORGET DATETIME everybody! It's death in database column type form!

― Andrew (enneff), Wednesday, 29 September 2004 15:50 (twenty-one years ago)

is datetime just a time_t ?

This is one of those computer science things I get obsessive about. I wanna have a datetime datatype with a minimal unit of time safe for scientists to use for storing particle physics shit. And I want the range to be able encompass the history of the entire universe

xposssst

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:52 (twenty-one years ago)

i think just having a time_t would be best -- that way people have to write strong code....

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:53 (twenty-one years ago)

i just used the online postgres docs but then i think i qualify as a moron based on the above definition.

>DATETIME is fucking bullshit because all you need is the number of seconds since Midnight, January 1st 1970. It's by far the simplest way of doing anything ever!

that's ok for you young'uns born after 1970 but what about the rest of us? or historians, what would they use? 8)

― koogs (koogs), Wednesday, 29 September 2004 15:54 (twenty-one years ago)

why would you store a number as letters?

― Ed (dali), Wednesday, 29 September 2004 15:54 (twenty-one years ago)

How did you learn DB/SQL in the first place? Osmosis?
rtfm.

No seriously, install mysql or something and go from there reading the manual till its set up and working for multiple users from another application.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 15:55 (twenty-one years ago)

seriously---

IIRC the original mac's datetime type was clocked to 1859 -- when the US Naval Observatory started keeping records I think.

I'd really like a good datetime type. I'm sure OS X's datetime type is something fun.....

Also, what datetime type do computers in time machines use?!?

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:56 (twenty-one years ago)

they need a signed integer

xpost

― ken c (ken c), Wednesday, 29 September 2004 15:56 (twenty-one years ago)

roffle! old people at negative time

― Professor Challenger (ex machina), Wednesday, 29 September 2004 15:56 (twenty-one years ago)

storing numbers as letters? For stupid as reporting languages with only partial java support would be one.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 15:57 (twenty-one years ago)

indicating "borrowed time"

― ken c (ken c), Wednesday, 29 September 2004 15:57 (twenty-one years ago)

I find them very useful, but it depends what you're up to. Oracle, which I mostly work with, provides plenty of good functions by now for handling dates. I think it stores it as the number of moments (don't know if it's whole seconds or not)since some date, but I don't need to worry about that.

It annoys me when people use varchar for things that are just dates, as they rarely make it impossible to put nonsense in there, and that makes writing simple code very hard, as you're always having to allow for records with '00000000' or some such in the date columns.

― Martin Skidmore (Martin Skidmore), Wednesday, 29 September 2004 15:58 (twenty-one years ago)

= geekiest thread evah!

― {Sand in the [vaseline} on the lens] (x Jeremy), Wednesday, 29 September 2004 15:59 (twenty-one years ago)

I can install both MySQL and PostgreSQL no problem, set up DBs, permissions, tables etc. But how do I jump from there to making use of the data. How the hell do I write complex queries comparing two tables and getting out the bits I want, for instance?

― Ed (dali), Wednesday, 29 September 2004 16:00 (twenty-one years ago)

select a.field_you_want_to_see_from_table_a, b.fieldyouwantfromtableb

from a, b

where a.linkthisfieldfromawith = b.thisfieldfromb

― ken c (ken c), Wednesday, 29 September 2004 16:02 (twenty-one years ago)

and variations thereof, SQL is probably the easiest bit of all

― ken c (ken c), Wednesday, 29 September 2004 16:02 (twenty-one years ago)

if you can do the dba the rest is gravy. Just keep reading the man page. Subquery and join are your friends.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 16:03 (twenty-one years ago)

if you use varchar for date you loose add_monthes, next_day, monthes_between and all sorts of fun though.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 16:04 (twenty-one years ago)

Plus dates in oracle transparently store timezone information.
Supposedly.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 16:04 (twenty-one years ago)

http://www.w3schools.com/sql/default.asp

any use? it's all just selects at the end of the day (or updates, inserts)(actually, i guess the WHERE clause is common to all these and that's where all the comparisons happen so...)

look into Third Normal Form as well

― koogs (koogs), Wednesday, 29 September 2004 16:07 (twenty-one years ago)

that's ok for you young'uns born after 1970 but what about the rest of us? or historians, what would they use? 8)
A quick check confirms that oracle does not use the old UNIX way of counting time.

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 16:07 (twenty-one years ago)

normalisation is fun!

― ken c (ken c), Wednesday, 29 September 2004 16:08 (twenty-one years ago)

and painful!
The best kind of fun!

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 16:12 (twenty-one years ago)

fun fun fun. I know there is a better way to do this but I forgot.
TO_CHAR( (select OPENING_DATE FROM PERIOD WHERE ID = '&FROMDATE'), 'fmMonth DD') || ' - ' || TO_CHAR( (SELECT CLOSING_DATE FROM PERIOD WHERE ID = '&TODATE'), 'fmMonth DD, YYYY')

― Mr Noodles (Mr Noodles), Wednesday, 29 September 2004 16:12 (twenty-one years ago)

I love these threads. I think it's a weird but harmless fetish. OR IS IT???

― Markelby (Mark C), Wednesday, 29 September 2004 16:35 (twenty-one years ago)

it's our livelihood mark

― ken c (ken c), Wednesday, 29 September 2004 16:53 (twenty-one years ago)

speak for yourself, i do it for fun 8)

ed, linux, apache, mysql, php and, importantly, something concrete to do (yet another cd database?) and just get stuck in. sql isn't rocket science. php lets you see real results as real web pages fast. edit, ZZ, refresh, repeat.

― koogs (koogs), Wednesday, 29 September 2004 17:28 (twenty-one years ago)

I am so glad I never got stuck having to learn dba skills. Then again DBA ppl in my sector are getting paid in the low six figures to start.
I was going to build OpenACS on my box at home but I have old version of dev toolkit and no idea how to start updating gcc from scratch.

― TOMBOT, Wednesday, 29 September 2004 17:37 (twenty-one years ago)

> no idea how to start updating gcc from scratch

compile it. oh, wait... 8)

― koogs (koogs), Wednesday, 29 September 2004 17:37 (twenty-one years ago)

Should I wait until tiger comes out or just buy a barebones x86 system and boot knoppix to fsck around?

― TOMBOT, Wednesday, 29 September 2004 17:38 (twenty-one years ago)

Seconds since 1970 might seem like a good idea, but it can lead to all sorts of problems. I managed to seriously fuck up iCal with dates past 2038 and prior to 1970 when I was working on Mac stuff. Mind you, it's more sensible than the Symbian time type, which = microseconds since the start of 0AD. And if absurd accuracy from a nonexistent date wasn't crackers enough, you have to apply a 15 day correction to deal with the Justinian/Gregorian changeover.

I used to have know too much about databases, but my knowledge has gone rusty through blessed neglect.

― Ricardo (RickyT), Wednesday, 29 September 2004 18:03 (twenty-one years ago)

> Should I wait until tiger comes out or just buy a barebones x86 system and boot knoppix to fsck around?

if this was slashdot i'd say gentoo!

aren't there binary openacs for mac available or is that missing the point?

isn't there a knoppix-alike for ppc? (um, yes, kinda, http://debian.tu-bs.de/knoppix/powerPC/)

― koogs (koogs), Wednesday, 29 September 2004 18:17 (twenty-one years ago)

edit, ZZ, refresh, repeat.

I love you, vi friend!

― Professor Challenger (ex machina), Wednesday, 29 September 2004 18:21 (twenty-one years ago)

(except i would never ZZ in real life. :wq all the way!)

um, all the LAMP tools (apart from the L!) should be available on macs and things like postnuke / phpnuke inhabit the same solution space as openacs (don't they?)

― koogs (koogs), Wednesday, 29 September 2004 18:32 (twenty-one years ago)

Gentoo: classic

I'm also one of those people who learned SQL (and PostgreSQL) by osmosis. I found (the hard way) that sometimes, the obvious way of phrasing a SELECT query can be 10 or 50 times slower (on PostgreSQL) than the non-obvious way; so it can pay to play about a little.

― caitlin (caitlin), Wednesday, 29 September 2004 19:50 (twenty-one years ago)

phpnuke is fucking gay.

thanks for the PPC knoppix link!

― TOMBOT, Wednesday, 29 September 2004 19:58 (twenty-one years ago)

that's ok for you young'uns born after 1970 but what about the rest of us? or historians, what would they use? 8)

Numbers below zero? Really, this is very simple.

― Andrew (enneff), Wednesday, 29 September 2004 23:55 (twenty-one years ago)

except that time_t is an unsigned 32 bit int. I suppose you could make a signed 64 bit big_time_t and keep the same zero date....

― Professor Challenger (ex machina), Thursday, 30 September 2004 00:05 (twenty-one years ago)

I luv my "SQL for Smarties" which is a whole book structured around funny things about how SQL does and doesn't do relational algebra right, and also teaching you to think in three-valued logic constantly.

I wanna write/design a three-valued logic language where all variables are tables, but which is procedural in evaluation. And then throw in easy ways to do web output/input, forms, etc. It would overtake Php/MySQL in a heartbeat. I'd call it PiQL -- procedural instantiated query language. (pickle). I'm not sure how it would implement the relation between database granular locks and java-type threadlocks, but they'd absolutely be linked.

Normalization ends up being thrown out the window usually in real-world large scale applications, since databases get optimized for particular query/input types.

Man I could so fucking be a DBA for real cashmoney instead of my job now.

Hardest code nightmare lately -- writing around SQL's lack of an outerjoin in the table types and version we're using. A basic query of a funny schema turned into a f-f-f-ing nightmare. It was tempting to throw in the towel and solve some of the problems in procedural calls, and I think I eventually did.

― Sterling Clover (s_clover), Thursday, 30 September 2004 05:39 (twenty-one years ago)

> Numbers below zero? Really, this is very simple.

and dates before (does calculation on fingers...) 1902? what then? 8)

> except that time_t is an unsigned 32 bit int. I suppose you could
> make a signed 64 bit big_time_t and keep the same zero date....

and you thought the y2k bug was a big deal... 8)

> phpnuke is fucking gay.

er, ok 8) i find it does usually require a bunch of customisation or it ends up looking exactly like all the other sites done using it. plus it was originally written for old versions of php and could do with a serious update (disclaimer: i haven't used it in 2 years) but it's a start.

oh, and the beauty of developing in php is that you can :w and hit refresh button and not even bother with the :wq mentioned above (unlike, say, what i'm doing at the moment which is 'ant all, (wait 3 minutes whilst it compiles), ant deploy, (wait 3 minutes until jboss deploys the new ear), ant get, ant package, ant run', rinse, repeat. sigh)

― koogs (koogs), Thursday, 30 September 2004 06:46 (twenty-one years ago)

ive f**kd up my php installation somewhere and cant get php4-mysql to work... take a look at my installation below
can anyone help?

http://www.willdabeast.co.uk/phpinfo.php

― willdabeast, Thursday, 30 September 2004 07:06 (twenty-one years ago)

It seems you've got PHP compiled with the flag "--with-mysql=shared,/usr" ... I don't know if that's a valid compile flag or not. Mine is simply "--with-mysql=/usr", so you may need a recompile.

― Andrew (enneff), Thursday, 30 September 2004 07:16 (twenty-one years ago)

'shared' would be referring to an environment variable, wouldn't it? Loads of the arguments have it in, so it's either intentional and not the problem, or 'shared' isn't defined anywhere.

Meh, this is probably not helpful.

― Core of Sphagnum (Autumn Almanac), Thursday, 30 September 2004 07:22 (twenty-one years ago)

thanks! it was just my crappy php scripts.

Wil

― willdabeast, Thursday, 30 September 2004 07:55 (twenty-one years ago)

It's finding the time to do it. I've got a dual processor P3 server runnign gentoo at work and projects a plenty to do, plus my ultrasparc at home. I do like the soft comforting feel of paper. However, links to good web PHP/PostgreSQL tutorials would be appreciated.

― Ed (dali), Thursday, 30 September 2004 10:33 (twenty-one years ago)

Well, ant is a bit pish, and has always seemed deathly slow whenever I've used it. Is there no way you can convert from ant to make?

― caitlin (caitlin), Thursday, 30 September 2004 10:44 (twenty-one years ago)

use the php 'DB' abstraction layer (from pear.php.net) and the database you're using becomes pretty irrelevant bar the connection details.

php.net has a php tutorial. there's a starter on webmonkey as well and an sql tutorial posted above. plenty of other listed by google. i started with phpnuke and customising that then threw it all away and started rolling my own front ends to various dbs (cd database, xml db, blog...).

― koogs (koogs), Thursday, 30 September 2004 11:03 (twenty-one years ago)

> Is there no way you can convert from ant to make?

am being forced to use netbeans on win2k (and work with 3 other people who wouldn't know the CLI if it bit them) so make's not an option. in netbeans it's just a double click to run things BUT there are 6 things to run, half of them conditional. the deploy is the killer as you have to manually check that jboss has finished (and correct as 25% of the time it fails)

― koogs (koogs), Thursday, 30 September 2004 11:09 (twenty-one years ago)

ant seems to become far more useful in large scale builds then makefiles.

― Mr Noodles (Mr Noodles), Thursday, 30 September 2004 12:09 (twenty-one years ago)

makefiles are for declan

― ken c (ken c), Thursday, 30 September 2004 12:18 (twenty-one years ago)

Don't knock the makefile.
For one programmer once wrote a Makefile and everyone else has cped it.

― Mr Noodles (Mr Noodles), Thursday, 30 September 2004 12:37 (twenty-one years ago)

Nowadays, Unixy types just write Makefile.am's instead.

― caitlin (caitlin), Thursday, 30 September 2004 13:42 (twenty-one years ago)

i hate make

― Professor Challenger (ex machina), Thursday, 30 September 2004 13:44 (twenty-one years ago)

make hates you.

― Mr Noodles (Mr Noodles), Thursday, 30 September 2004 15:37 (twenty-one years ago)

the one thing i don't like about ant is the lack of a password input option, something identical to the input option but which doesn't echo what you type to the screen in plaintext for everybody to see (i was writing deploy scripts that copied stuff to production servers, something that needed to be restricted)

(yeah, you can write your own ant plugins but i didn't want to have to learn the java to do it!)

and also some way of forcing copies etc - it would often not do things because it deemed them unnecessary.

makefiles can be massively complex, the one at the last company was a bitch that only one person knew how to modify (and he wasn't always sure). that said, the build environment was largely to blame - it required >6 screenfulls of cc -I compiler options!

― koogs (koogs), Thursday, 30 September 2004 15:58 (twenty-one years ago)

Fatal error: main(): Failed opening required 'fns.php' (include_path='.;/usr/ucc/lib')

WAY TO DELIMIT INCLUDE PATH WRONG

― sometimes i like to pretend i am very small and warm (ex machina), Wednesday, 13 October 2004 16:01 (twenty-one years ago)

Some systems do use semicolons instead of colons. Or are you referring to the apostrophe?

― Mr Noodles (Mr Noodles), Wednesday, 13 October 2004 16:54 (twenty-one years ago)

in php the proper delimiter is :

― sometimes i like to pretend i am very small and warm (ex machina), Wednesday, 13 October 2004 17:33 (twenty-one years ago)

two weeks pass...

I'm a moron. Does anyone want to help the DEMOCRATS and me and help me refine some code? hit me up on AIM ---> teenydreams

― teeny (teeny), Monday, 1 November 2004 00:01 (twenty-one years ago)

two years pass...

"I'll name all the tables in arcane NAME$xxxxxxx tables and NOT DOCUMENT IT" so the client will always have to pay me while I look on GMANE for some relevant code"

Stupid fucking jackass DBA consultants... Go hurl yourself off the top of a building.

― Elvis Telecom (Chris Barrus), Wednesday, 8 November 2006 00:26 (nineteen years ago)

I just want to say that even though I have a bachelor's degree in computer science, have a really awesome IT job, and am currently studying for a SQL Server 2005 certification, I still have absolutely no fucking clue what any of you guys are talking about on this thread.

― Mr. Snrub (Mr. Snrub), Wednesday, 8 November 2006 03:24 (nineteen years ago)

That's why you have to waste your life in the ways you describe.

― Eyeball Kicks (Eyeball Kicks), Wednesday, 8 November 2006 03:37 (nineteen years ago)

I haven't written a single line of SQL string mangling since moving to an ORM and have never been happier.

― roc u like a § (ex machina), Wednesday, 8 November 2006 04:33 (nineteen years ago)

You must be logged in to post. Please either login here, or if you are not registered, you may register here.