Good day... Gotta a quick question here if anyone's around...

well actually might be a bit more than a quick question... I've just got a mssql database that comes from 1988 with several transitions from Dataease into access into mssql... and I need to build a new frontend but I noticed just now... there no fk constraints...

so my question is, I guess... where should I start? Or under some very special circumstance should I preserve this beast?

hackernoon.com/…

@NelsonCasanova is there any documentation?

is there code available from any application that access the database?

There's only a somewhat old Access frontend... ADE file

some kind of compiled access file, can only see the tables, so no good... thats it pretty much... by querying a bit I can tell there's all sorts of evil in this thing...

Im a bit lost, Im thinking maybe I can handpick the biggest tables and more important ones and recreate them on a new database, import one by one from the ones that should be core (Personal, Payments, etc) and see how that goes... or maybe not :s

How big is the design, how many tables?

Are they named sensibly (like Personel, Payments you mentioned) and the columns?

I had a friend who in his first job, he had to use the Access databases made by the boss, which were all named George-1, george-2, etc.

The same names for tables and columns ...

The boss name was George

Everything was George-X in tens of dbs

@ypercubeᵀᴹ hahaha omg that is hilarious

53 tables, about 10 of them seem to be main tables and I've got 2 that hold about 80% of the records... I can spot about 4 different naming conventions...

but yeah, one could say its not that hard to spot the main ones

Doesn't look so bad.

If there columns like PersonID, PaymentID, ..., you can conjecture that they are FKs

and then test for incosistencies

It wouldn;t hurt to post a question at the site. Someome might know

I rememebr a similar question but I can't find it now.

@ypercubeᵀᴹ I found this one: stackoverflow.com/questions/12065526/…

Related, but I don't see any way to find them, if they are not defined/documented

but you're right... I'll focus on the Person, for a start, make sure there's no duplicates, if any sort them out across the database and then move on until I've got appropriate virtual relationships, then I'll create the constraints and see whats with that

long night....

@NelsonCasanova You can try to automate some stuff.

Like first count how many rows each table have.

(and save wverything you find somewhere)

in a board or something

Small tables will probably have other tables that reference them.

Big tables will have FKs that reference other tables.

Check datatypes. Floats,decimals,datetimes are raely FKs or tagets of FKs.

@NelsonCasanova Oh and do the tables at least have PRIMARY keys?

and other constraints (UNIQUE, CHECK)?

@ypercubeᵀᴹ they have, yeah, some of them

but its just... on one table I have PersonNo and another personid and then PersonID

hahaha this is just.. but I can correlate most of the records on the Person table with the records on the other tables, a very small amount of PersonNo's-Id's dont seem to be on the Person table... so yeah... thats not bad

@NelsonCasanova and all the same type?

Is there any application that uses the db?

yes, an access FE

I figure I could profile some of the functions

If it working, you can use it and see what functionality is there

cuz I cant really make changes or see the forms in design

I haven't used Access for ages, so not sure how or if you can see source code.

But if you can operate the thing, you can go and add a Person or make a Payment and see what changes in the db

(after you take a backup of course!)

@ypercubeᵀᴹ I hate access so much, I started at my current job fixing Access Frontends... I learned to hate it with all my soul... every time I think about quitting my job or life, Access is in the mix

@NelsonCasanova hah ;)

You could come to the Heap in the next days,again. Not very busy at weekends but I rememebr 1 or 2 uers that have expience with Acces. They might be of bigger help than me

Got to go now

Good luck with the project!

ok man, thanks for your help

I'll be around

@MikaelEriksson Thanks. I was surprised and a little irked by the official response at the time. And yes, they have been historically reticent about fixing bugs/oversights for various reasons, but just recently that has changed a bit. Perhaps that issue will be corrected at some stage. I certainly hope so, and will continue to advocate for it.

Paul Little-Bit-Irked White

Ha.

Mornin'

(My phone corrected that to "Mormon" the first time)

@ypercubeᵀᴹ Nice edit to the deterministic question. Only just catching up.

> Comments resulted in an answer, please clean up dear mods, thank you, much appreciated. Mods like the better class of users asking nice and thanking in custom flags. We like our mods. Thanks again!

LOL custom flag.

@Phil Mormon.

@PaulWhite That's the seond one, right?

from @TomV?

morning

@PaulWhite What threw off at first, was that DATEDIFF, day, 0, some_date) is deterministic but DATEDIFF(, day, '19000101', some_date) is not.`

Having read all of @AaronBertrand posts about how good is to use the YYYYMMDD format. He certainly knows this weird case where 0 works while the YYYYMMDD fails ;)

(and oh, several missing parenthesis and extra commas there ...)

@ypercubeᵀᴹ Yes.

@ypercubeᵀᴹ Using 0 there is 'offensive' to me, but the implicit conversion to datetime is deterministic.

Nice: simple-talk.com/sql/t-sql-programming/devil-wrote-code

Poorly-formatted code is rarely good code in my experience. And rarely written by someone competent.

@PaulWhite Haha

Sorry :)

Trolls be trollin'

Looks like a useful template for future custom flags to me :)

@PaulWhite No seriously, not joking, I (and I think I can say we) really appreciate our mods, you and the others too

There must be quite a bit of effort involved, and maybe not enough reward

@TomV Oh there's plenty of reward. And we all knew what we were getting into, and can stop at any time, so it's no biggie.

But thanks.

We appreciate the non-mod users as well. They make the job significantly easier than on other similarly-sized sites, I'd say.

</mutual love in>

@TomV regarding what we were discussing the other day, about manuals: PostgreSQL manuals

@ypercubeᵀᴹ I couldn't find a good duplicate either. Was surprised.

I wonder how long Rolando's ! key lasts on average.

@PaulWhite The default is 3 I think ;)

Give it a Try !!!

@ypercubeᵀᴹ I mean I wonder how often he has to replace it, physically.

Ah ... didn't get it

LOL

Like in Spanish, where exclamation and question marks are also in the start of a sentence (inversed), in Rolando, it's 3 times !!!

@ypercubeᵀᴹ What is going on with your keyboard lately?

Or are you on a phone or something?

@PaulWhite Today I'm on the PC, normal keyboard. I have been using lately a laptop, in the bus or train, so that explains the typos. (is that what you mean, typos?)

Yep, skipped letters, oddly placed spaces...

"th start", Rolnado

Just wondered, doesn't bother me at all

The font size is probably also an issue. I'll have to change it. Didn't even notice the Rolnado, otherwise I would have edited.

CTRL+, CTRL+, CTRL+

High DPI display?

@PaulWhite I guess. I have to calculate.

Is 100 dpi high?

That can be our dupe target now.

@ypercubeᵀᴹ Don't think so. Some people have flash very high DPI (4K?) displays.

e.g. 3200 x 1800

must be my deteriorating vision then

I run 1920 x 1080 on 15" at 175% (Windows) scaling.

I have a small laptop screen and a 22"

I've often though about getting a larger external display, but I would hardly ever use my laptop with it I think.

player.vimeo.com/video/41225777?badge=0

Stop at 00:58 and find bluefeet!

ha ha ha awesome

MySQL doesn't have indexed views?

Oh nvm. Like I care anyway.

@PaulWhite No.

Computed columns only. So kind of.

omg

oh, silver sql-server somehow

Where did I steal that

can I somehow tell mysql/innodb to choose a data directory for each table, from a list of directories I provide?

I have the issue that the server host I use allows me to only generate multiple volumes of a maximum size, my total database size can exceed that limit

@CBenni dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html

yeah exactly that. Id like to automate that. I guess it isnt possible?

I might try to use LVM or software raid to just bind the disks into one large disk

@CBenni I think you may use this as well: dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html

Or better, ask a question at the site

since I can dynamically add more disks later

If the volumes have a max size, perhaps you can create fixed, large tablespaces, without autoextend.

@CBenni Sorry, I meant this link: dev.mysql.com/doc/refman/5.7/en/…

which has examples of how to setup tablespaces with and without autoextend.

And I have no idea how/if it would work with file-per-table setting

mh

ill post a question on the site, its ... kinda hard to phrase, which is why I asked here first ;_;

forums.mysql.com/read.php?22,21369,21369#msg-21369 seems like it

mh, never mind, only works if file_per_table is disabled