Pip Database Structure

Pip

Jul 10, 2015 17:26

oh god SQLite doesn't support arrays

Hjorthenify

Arrays?

Pip

like... a collection of values

I could do that in PostgreSQL

Hjorthenify

select?

Pip

no, like I can't go: (typing)

class Foo
{
    Bar[] _bars;
}

I can't put that in a DB

Almo

does mySQL support putting an array into a DB?

honest question

Pip

Jul 10, 2015 17:28

I have no idea; I'll look it up

Hjorthenify

Don't think it does

user4704

"Yes."

Pip

nope, but you can imitate with a bunch of tables. I think

user4704

But it depends on how you expect that array to be put there.

Pip

oh I can also store it as a blob.. maybe

@IcyDefiance input?

Icy Defiance

Jul 10, 2015 17:30

make a Bar table?

Hjorthenify

You'll have to write a wrapper @Pip

Pip

Oh that's true.... hm

Mind if I spit some sample data at you?

Icy Defiance

vOv

Hjorthenify

Not at all

Be aware though that my drink just arrived at the café and I'm not sure when I'll respond :P

Almo

or how coherent the eventual response will be

Pip

Jul 10, 2015 17:34

welp

too long for SE

Hjorthenify

Haha indeed

Pip

pastebin.com/kEUDMX6f

There's the header and one data element thing for the Best VS catagory

or... two data elements I guess

It looks like this

Hjorthenify

Okay.. Sorry i can't read that from the phone properly :P

Pip

There are 8 of those, how would I store them?

@Hjorthenify ah kk. The picture illustrates it well

dotabuff.com/heroes/abaddon <- the "Best Versus" section

Hjorthenify

Well make a column for each data you wish to store in a Foo table :P

Pip

Jul 10, 2015 17:38

that works I guess. Thanks

Hjorthenify

@Jon helped me a lot with SQL

Pip

good to know

Almo

hey on sql

if I have a table of (ContestID, UserID, Entry) where there can be duplicates of any one of these, but not a row where all three are the same, does an aggregate index of all three mean that I can have a fuckton of rows and SQL commands on it will still not become super slow?

user4704

Maybe.

Pip

I... think so?

user4704

Jul 10, 2015 17:44

What's "Entry"?

Almo

this is a potential issue with LowDown... the Entry table grows without limit, but there can't be exact duplicate rows.

they're all ints

user4704

k

Pip

user4704

Are you clustering on that index?

Almo

I don't actually have an index on it, and I don't know what clustering is

I'm looking to deal with this issue now

user4704

Jul 10, 2015 17:45

A clustered index is an index that impacts the physical storage of the data.

Almo

either by some clever indexing thing, or by running a cron job once a month to archive old entry data.

Pip

but then how do I reference the "Best VS" and "Worst VS" tables in the "Hero" table? @JoshPetrie @Hjorthenify

user4704

A regular index just says where to find rows.

user4704

A clustered index puts rows with similar indices next to eachother

Almo

so I could make the contest id the index (not unique), but set it to cluster, then it will know to "sort" them by that index?

user4704

Jul 10, 2015 17:46

What sort of queries are you doing on the table?

user4704

yea basically

user4704

it's somewhat more complex than that but that's the gist

Icy Defiance

@Pip other way around. the best/worst tables would have foreign keys to the hero.

Almo

just a sec

user4704

@Pip Give the best/worst... yeah what he said

Pip

Jul 10, 2015 17:47

oh I see... hm

Icy Defiance

@Pip also you can probably merge the best/worst tables with a char(1) column that is 'B' for best and 'W' for worst

if you want

Almo

    $entryQuery = "SELECT entry, COUNT(*) as count FROM ld_entries WHERE contestid = $contestid GROUP BY entry ORDER BY COUNT(*), entry;";
    $entryResult = mysql_query($entryQuery) or die(mysql_error());

Pip

@IcyDefiance oh that's clever

Almo

                $winnerQuery = "SELECT ent.userid
                                FROM ld_entries AS ent,
                                     ld_users AS usr
                                WHERE ent.contestid = $contestid
                                  AND ent.entry = $lowestNumber
                                  AND ent.userid = usr.userid";

stuff like that

user4704

An index on contestid would probably help there

user4704

Jul 10, 2015 17:49

I woudl start with a simple, single-colum non-clustered index

user4704

and get more specific/complex from there

Almo

ok, I'll do that then. thanks :)

user4704

Make sure to check out the .. acually if this is mysql you don't have the query profiler tool I'm thinking of

Almo

yeah it's mySQL

user4704

I haven't really used that extensively but the same basic concepts should apply

Almo

Jul 10, 2015 17:50

ok thanks dude

Pip

So, for my question

if we swap the users with heros

would that be the ideal table layout?

Icy Defiance

sounds reasonable

Pip

The active_relationships table would have the VS info, like "Worst/Best", the advantage, win rate, and match quantity

user4704

Something like that, yes.

Pip

Would you make any modifications?

user4704

Jul 10, 2015 17:53

you only need active_relationships if you have a many-to-many relation between hero and worst.

user4704

or hero and best.

user4704

which it sounds like isn't the case?

user4704

one hero can only have one best and one worst time, right?

Almo

Complex SQL queries are fucking magic I tell you.

Pip

There are 110 heros

each one has it's 8 best vs and 8 worst vs

Almo

Jul 10, 2015 17:54

hahahah LoL has more heroes. hahahaha

;)

user4704

ah i see

Pip

@Almo you sh (/bin/sh to be exact) :P

@JoshPetrie Yeah, that's my issue. I can't store all of the heros recursively like that

user4704

Then you probably need a hero table, which has an ID, a "best against" table with two hero ID keys

Almo

^

Icy Defiance

active_relationships (call it matchups maybe?) could have a FK hero, FK against, then win rate and whatever metadata you want, and you could get the 8 best and 8 worst by sorting by win rate

Pip

Jul 10, 2015 17:55

oh I see

user4704

and a "worst against" table

user4704

Which is similar.

Pip

mhm

user4704

you MAY be able to collapse them into a single table

Pip

I don't think so

user4704

Jul 10, 2015 17:56

"RelativePower" with a hero foreign key, a subject key, and 0 or 1 or whatever to indicate "good against" or "bad against"

Pip

A hero being bad against another doesn't mean it's in the top 8 "best against"

I like that I think

user4704

But whether or not that's a useful idea depends on how you're going to query the data.

Pip

Basically I'm using this for a teambuilder

user4704

So RelativePower row of 1, 3, 0 might might hero 1 is good against hero 3. But 1, 3, 1 means hero 1 is bad against hero 3.

Pip

so if you plug in a team of heros it'll give you some recommendations based on what heros are good against them

and also other factors, but that's the major one

user4704

Jul 10, 2015 17:57

If you're going to tend to want to look up both who is good and who is bad against a given hero, I'd do it in one table

Pip

Alright, cool

user4704

then you can just select * from RelativePower where hero = whatever"

Icy Defiance

since we're all talking about programming problems now, apparently removing metadata from jpegs (to keep personal info from being uploaded) totally screws them up when they use non-rgb color formats, like cmyk or ycck

Pip

So, let me recap. I have a table with "hero id", "opposing id", metadata, "best/worst". Correct?

Icy Defiance

worse, the ColorSpace tag is under the same "application marker" as gps coords, so I can't just not remove that part

user4704

Jul 10, 2015 18:00

yea

Pip

thanks a lot!

Game Development

Participants