IPB

Welcome Guest ( Log In | Register )

4 Pages V   1 2 3 > »   
Reply to this topicStart new topic
> Database Design / Data Entry
Taren
post May 8 2009, 07:26 PM
Post #1


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




I've taken the liberty of designing and setting up a database for the Reincarnation of VJ Army (see what I did there?). In doing this, I have taken into consideration all of the curveballs we've seen in the last 5 years to make the db as robust as possible while minimizing complexity.

http://vja2.superwailingbonus.com/
Any parties interested in filling in song data and chart info should register here. Let me know your login name, and I'll give you admin rights, so you can screw around with the data. The pages provided are a generic UI that can be redesigned as necessary. All interactions with the database are handled through function calls, so there are no queries being made on these pages themselves.

Disclaimer: I don't expect the new VJA to be at this address; I'm simply using this as a sandbox to build a design. When we decide where to go and what to name it, I can export everything to be hosted somewhere else. (Everything is being done through mysql)

I could provide a schema or something to show my ideas, but it'd probably make more Sense to explain it in English.

account - each user is identified by a unique login id. The user can change their "displayed" name, or dj name, but dj name must be unique. Right now users can only use [A-Za-z0-9] in names, but once I figure out what all the legal chars are, I'll allow them.

style- a list of style abbreviations and full names. e.g., (3rd, beatmania IIDX 3rd style)

song- a list of title/artist/genre. No chart info stored here

chart- accessed by clicking "edit charts" on the song management page. Each song is associated with a number of charts. Each chart *should* have a meaningful comment (old hyper, new another, etc.), a notecount, bpm min/max (these change between charts sometimes, like one of the older Era songs), and "official" rating (usually hibroad).

appear_chart- each chart may appear once per style. On the chart management page, this appears as a list below the song data. Check off which styles a certain chart appears in, which mode it appears as (some change, such as 5.1.1. Normal), and its rating IN THAT GAME. 5.1.1. Another7 appears as a "1" in 7th style, a 6 in 10th style, and a 10 in HS.

course- expert course title, mode (normal/hyper/another?), total notes, total difficulty

appear_course- which courses appear in which games under what modes? (might be useful for Hell courses that have only one diff in older styles but multiple in newer styles)

course_stage- references to individual charts, and their positions within the course. This will be useful for longer courses (see: 6th style)

Things this design can handle, and instances we've already dealt with:
-A single chart gets updated for no reason whatsoever (Snow, Music To Your Head, GMD, The Shining Polaris)
-A chart gets shifted to a different mode (5.1.1. 3rd Style 7K -> 10th Normal)
-A chart gets a different diff rating on different styles (Pretty much everything above 6; primarily Lovely Storm)
-A song gets an extended version (the Sota charts on DD would still qualify as being charts for the same song)
-A song gets a wonky Another with a different BPM (Karma, Quell, Outer Limits)
-A song gets an Another that goes on to become its own song (EMM/EMP, Lovely Storm)
-A chart gets adjusted between styles and may need to be scored separately from the original (Photongenic?, Love is Eternity)

Things this design will facilitate:
-Sorting by difficulty within a single style (view all 6's from 5th style, view all 8's from 10th style, 9's from USA)
-Allowing users to enter in scores for a chart from multiple versions (we already do this)
-Removing redundant song entries from the list (old Anothers, Black Anothers)

Things this design can't do:
-Those little + symbols for the old flashing-whatevers. 5th-9th style flashing-7's will have to entered as 8's, and RED's flashing 8's will have to entered as 9's. Then we can simply write a quick function to convert them back.

I have a bunch of tables regarding score entries, but I haven't even started on courses yet.

This db backend should be able to support the next IIDX score tracker, wherever it ends up going. What we'll need next is a person/team focused on the page design (look & feel, features, etc.) and another team focused on entering in song/chart/course data. Importing player scores could get a little tricky, but that's not really important at this point.

Does anyone have any input on this design? Has anyone else built anything db-wise? Would anyone like to help?


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 8 2009, 07:35 PM
Post #2


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




I'm thinking we should probably hold off on this until we get the source code from Remy, right?

Unless you feel like you have time to rebuild a new version of VJA from scratch using the source as a base in which case more power to you, but I thought we were planning to put up the current source as it is and then make modifications to that.


--------------------
Go to the top of the page
 
+Quote Post
Taren
post May 8 2009, 07:44 PM
Post #3


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




I figured we could use the source as a general guide for doing stuff. I really don't expect it to be that hard to redesign it from scratch, but I will need help with some things like the rival graph on the score entry screen. I've just felt like some of the site deserves a good face-lift.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 8 2009, 07:48 PM
Post #4


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




The rival graph is all Javascript that was written by jammitch, IIRC.

And yeah, I guess I'm just more in the philosophy of maintaining existing applications than making new ones. I guess it depends on what you feel you can do in the time you have - I'd like to have as little transitory downtime as possible after VJA closes, though.

And really, the main reason I would be waiting for the source is because we might need to migrate this data from the old format to the new, which is easier when you know what format it's stored in already.


--------------------
Go to the top of the page
 
+Quote Post
jammitch!
post May 8 2009, 07:48 PM
Post #5


Tranoid
****

Posts: 969
Joined: 6-September 04


DJ Battle This User

View User Page
Pop'n Battle This User




I think moving ahead with a new schema is probably a good idea. Splitting charts from songs is something that really needs to be done, and we might be able to improve performance if we know what we're doing (which I don't).

I'd like to see something at the style level to indicate features available in that game. We could start by filtering down available mods, and ultimately I'd like to be able to enter your sudden+ type-B number and have it be able to guess at your HS/SUD+ settings for a given song. This could also be used to indicate special difficulty for a game (8 = flashing 7, or anothers being marked as a + because they don't have real ratings).

Relatedly, we haven't gotten to the score part yet, but it would be nice to be able to enter your SUD+ numbers, as well as enter a private comment - I have a towel ruler on my TV and sometimes take notes about approximate towel positions for the BPM gimmick songs.

As I said, I'm out of practice with relational databases, but I'd love to work on the frontend. I have a few features in the Javascript parts of the existing code - the rival graph and some sorting code, namely. Might be nice to have a graphics guru as well - I'm decent but I don't think I want to do it all myself.

We can also probably import a LOT of the database by grabbing an XML dump from a user who has a score for everything, and writing a parser. Just so happens I have one sitting around - from the offline editor I still want to do someday - but it's in Java. We'd have to clean some stuff up to match the new DB schema but it would save a lot of time. Maybe if you can give me your schema (phpMyAdmin export) I can install it locally, mod the parser, and give you back an export of the data to start with.

I'm sure I have more to say, but the longer the post gets, the more likely it gets obsoleted, so I'll just say things as I think of them.


--------------------
Ode to RAM
ALL RIGHT, ACT HIGH and LOW
SWITCH to the LAB and FEEL IT SNOW



QUOTE
He had found a Slake-o-Matic machine which had provided him with a compact disc filled with a sound that was almost, but not quite, entirely unlike music.
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 8 2009, 07:51 PM
Post #6


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




Is it ethical to just download everyone's scores and put them into the replacement system? Or should it be a voluntary thing? My only concern with the latter is the rankings are going to look pretty bare for a while, it would essentially be starting from scratch.

As far as graphics go, we've still got to decide on the name of the site, which will probably lead into a graphical theme. e.g. if Solid State Squad gets chosen then we could go with a kind of futuristic/electronic look for the whole thing.


--------------------
Go to the top of the page
 
+Quote Post
Taren
post May 8 2009, 08:04 PM
Post #7


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




It would be pretty cool to see all of the data as it exists now, but I know some users that haven't logged in for five years... maybe leave it all out, and make XML imports voluntary?

jammitch: http://superwailingbonus.com/stuff/vjadb.sql.zip
This should be what you're looking for. I included the data too. I don't know how to do foreign-key constraints, so all FK's are implied at this point.

I also have a table for "mods", so we can specify which styles support Easy/hard/hazard, or DP Flip, or Sudden-Plus, but I'm not using it yet.

That's a good point about the score entry page, there need to be more options for things like green number or towelage.

I mean, it wouldn't be that hard to sit down and come up with a list of "required features", then decide what all has to be done to make them happen. That's what I did for the db design.

I don't know what my free time looks like over the next month; I'll be starting a "real job" soon, but until then I can give it my all.


--------------------
Go to the top of the page
 
+Quote Post
jammitch!
post May 8 2009, 08:05 PM
Post #8


Tranoid
****

Posts: 969
Joined: 6-September 04


DJ Battle This User

View User Page
Pop'n Battle This User




My suggestion was to grab the song data, not (necessarily) the scores.

We can't really grab the scores unless we retain existing user accounts, which would open up another can of worms. I think we have two approaches:

1) Use the existing code base with the existing database and be a straight continuation of the old site
2) New code, but allow people to do an XML import of their old scores


--------------------
Ode to RAM
ALL RIGHT, ACT HIGH and LOW
SWITCH to the LAB and FEEL IT SNOW



QUOTE
He had found a Slake-o-Matic machine which had provided him with a compact disc filled with a sound that was almost, but not quite, entirely unlike music.
Go to the top of the page
 
+Quote Post
Taren
post May 8 2009, 08:09 PM
Post #9


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




Oh! Right! Song data. In that case, use Chickensnack's account. That's what I had to do at one point to pull all of the songs. The only problem is that I don't think VJA supports the separate difficulty ratings between styles (5.1.1.[a] is rated 1, 6, 9, or 10 depending on the style) so a lot of effort would be required to fill in all those numbers.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 8 2009, 08:14 PM
Post #10


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




QUOTE (jammitch! @ May 8 2009, 09:05 PM) *
My suggestion was to grab the song data, not (necessarily) the scores.

We can't really grab the scores unless we retain existing user accounts, which would open up another can of worms. I think we have two approaches:

1) Use the existing code base with the existing database and be a straight continuation of the old site
2) New code, but allow people to do an XML import of their old scores


My personal best case scenario was to do 1) and then improve the site down the track, but I'm open to other ways too. I think it would be overall best to do 1) so as to minimise any downtime for the site, and then work on making all the changes to how the database works and migrating the old database data to the new format behind the scenes until it's done, that way we don't have to rush anything.


edit: Also, on rereading your schema don't forget a 'mode' table, and a table for assigning modes to styles. Since DJT proved that even difficulties can get added from style to style. Plus this would let you handle differently named modes (e.g. L7/7K/A versus N/H/A) without a hitch. When someone selects a style then a song you just query the mode_assign table and chart_assign table to see which modes to display in the 'difficulty' dropdown.

One caveat I can see with this approach in general is that searching for a song directly becomes more difficult, as you need to know what style the user is after as well. Maybe an additional choice after they choose the song?


--------------------
Go to the top of the page
 
+Quote Post
Taren
post May 8 2009, 09:17 PM
Post #11


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




Oh, the modes/mode names data will be in the same table with the available options. Forgot to mention that one.

Xythar- Yeah, I guess that would be an issue. Maybe have an additional dropdown box that would get autofilled with options if there are multiple charts?

Selecting 5.1.1. and then Single-Hyper would autofill this third box with "10th/hs" and "3rd/5th/7th/us" since there are two distinct chartid's that features a SP-H chart for 5.1.1.? Jammitch would have to help there; I think that requires javascript.

For "normal" songs like V, you could disable the third box or something. Just throwing ideas out there.


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 8 2009, 10:00 PM
Post #12


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




Nah, they'd have to be separate tables unless you want a boolean field to enable each mode for a style (I don't recommend it). I'm thinking just a modes, mods, mode_assign, mod_assign or such (the latter two are just foreign key pairs)


--------------------
Go to the top of the page
 
+Quote Post
itsgreylolol
post May 8 2009, 10:20 PM
Post #13


I am the end of the world
*****

Posts: 1,691
Joined: 24-June 07
From: Lubbock, Texas


DJ Battle This User




Manual xml output is good idea, that way the people who want to move can, and we don't take an unnecessary load with us.


--------------------
All numbers not including EMP+PB
[L7] Left to AAA: 3 (Step Into the New World, Fun, Gambol)
[7k] left to A: 1 (Pluto)
11*s left to clear: 9 (waxing and wanding, tripping contact (remix), The Dirty of Loudness, four pieces of heaven, Blocks, Bleeding Luv, Be OK [b])
12*s Cleared: 5
Ds left in IIDX: 4 (Mendes[b], Icarus[b], four pieces of heaven[b], Mei[a])
Go to the top of the page
 
+Quote Post
Taren
post May 8 2009, 10:29 PM
Post #14


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




What I was thinking for mods/modes is this:

Each mod has a predefined constant value. For example, Random is "R", Easy is "E", Hard is "H", Mirror is "M", etc. I already have a system like that for charts: 5, N, H, A, B, 10, ND, HD, AD, BD.

Each row of the mods table will have the style name (3rd, 4th, etc.) and then each field would contain a set of values, separated by commas or whatever. This would be the simplest way, and since these mods don't change over time, there's no need to make the tables flexible to mods that don't exist yet.

For example, the modes field for BMUS would be 5,N,H,A,10,ND,HD,AD. 3rd style would be N,H,A,ND,HD,AD. Troopers would be everything.

As for mode NAMES, there would be a separate field for that. For 3rd style, it would be "Light7,7Keys,Another(7),Light14,14Keys,Another(14)". Since they're only really used for displaying/song entry, the page can just query for the field, split it into an array, and use whatever it needs.


--------------------
Go to the top of the page
 
+Quote Post
jammitch!
post May 8 2009, 10:47 PM
Post #15


Tranoid
****

Posts: 969
Joined: 6-September 04


DJ Battle This User

View User Page
Pop'n Battle This User




If we do decide to redo the database (which I think is a good idea), I still want to keep the front end pretty close to what it is now, initially. I bet we can actually leverage the existing code and wrap it around the new database calls without too much work - except for this one issue.

For splitting one chart across multiple styles, as it is today you need to know which version of the song you want anyway so it won't be any worse. I don't know if song > difficulty > style is the right order to go in. Style > song > difficulty makes more sense, but you have to know the style the song is on so it's not an option that can be used all the time. Song > style > difficulty seems better, but it may be somewhat tricky to determine which songs need the style selection before selecting a difficulty.

This actually seems like we might want to do AJAX for, unless we want to load all the information for all the weird songs every time. That would be something I haven't done before.


--------------------
Ode to RAM
ALL RIGHT, ACT HIGH and LOW
SWITCH to the LAB and FEEL IT SNOW



QUOTE
He had found a Slake-o-Matic machine which had provided him with a compact disc filled with a sound that was almost, but not quite, entirely unlike music.
Go to the top of the page
 
+Quote Post
Taren
post May 8 2009, 11:54 PM
Post #16


patsenner
******

Posts: 2,012
Joined: 13-April 04
From: Queen Creek, AZ


DJ Battle This User




I agree we shouldn't deviate too far from the current design. Regardless of what happens, I am trying to stick with a tiered architecture so that regardless of the interface, everything has to go through a set of standard functions.

Xythar- you're talking about this page, right? http://vjarmy.com/iidx/viewsong.php
That sort of searching will not work with the way I'm designing the data because of the songs with wacky chart changes, but it shouldn't be that hard to adapt to.

Jammitch- can you combine PHP and AJAX?


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 8 2009, 11:57 PM
Post #17


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




Yeah, that.

Don't serialise all the modes and mods into strings like that. It's much better database design to just have a table to define each thing you can design and then a table for assignments. I'll show you an example of what I mean later on when I'm not typing this on my phone.


--------------------
Go to the top of the page
 
+Quote Post
Schala
post May 8 2009, 11:59 PM
Post #18


excessively entitled
*********

Posts: 5,925
Joined: 28-February 07
From: https://instaud.io/33hI




graphics should be updated to be more colorful since the worst thing about vja 4.0 was how ugly and lifeless it looked

imo the best would be a mixture of current graphics and some blatantly stolen from IIDX WR & WRDB










o I forgot


--------------------
Go to the top of the page
 
+Quote Post
Schala
post May 9 2009, 12:10 AM
Post #19


excessively entitled
*********

Posts: 5,925
Joined: 28-February 07
From: https://instaud.io/33hI




If you guys want to have a billion graphics, most of which only ALNLUK would care about, we could make customized versions of these (since some are in CHINESE)

Black 1P/2P images are cooler than blue/green imo since 2P side should not be associated with ugly green
>

KOC I'm guessing
I don't know if this is for "Arcade Controller" (probably) or actual AC but if you're going to allow AC scores there should be an AC/CS indicator as well
Popcon (setzer could use this for his "I AAA'd drunk monky [a] on pop'n asc" claim)
Toph is like the only dualshock player

The WR has flashing 1P/2P (like ) for one-handed play (I think) but imo it'd be better to make a localized versions of (RH) and (LH)


--------------------
Go to the top of the page
 
+Quote Post
chotto kiitena
post May 9 2009, 06:01 AM
Post #20


INFERNO
************

Posts: 14,907
Joined: 22-May 04
From: Melbourne, Australia


DJ Battle This User

View User Page
Pop'n Battle This User




So basically, I'm thinking something like this for the DB tables:

song
CODE
song_id        song_name    song_artist (etc)
1              5.1.1        dj nagureo


chart
CODE
chart_id    song_id        description                  notecount
1           1              5.1.1 3rd 7K, 10th+ L7       99
2           1              5.1.1 5th/7th style L7       83
3           1              5.1.1 7th+ style [a]         786
4           1              5.1.1 10th+ style 7K         511

(description is purely a comment field for table readability)

mode
CODE
mode_id            mode_name
1                  5 Key
2                  Light 7
3                  7 Key
4                  Another
5                  Normal
6                  Hyper
7                  Black Another


style
CODE
style_id        style_name (etc)
1              3rd Style
...
5              7th Style
...
10             Happy Sky


(NB: I had mode_assign here but after doing the next table I realised we didn't need it)

chart_assign
CODE
style_id    song_id        mode_id        chart_id    difficulty
5           1              2              2           1
5           1              3              1           1
5           1              4              3           1
...
10          1              5              1           1
10          1              6              4           6
10          1              4              3           10


And as far as mods go, I think something like this

mod_type
CODE
mod_type_id        mod_type_name
1                  Speed
2                  Arrange
3                  Gauge
4                  Display


mods
CODE
mod_id            mod_type        mod_name
1                 1               Normal
2                 1               HS0.5
3                 1               HS1
4                 1               HS1.5
5                 1               HS2
...
9                 1               HS4
...
12                1               LS1
13                1               LS2


mod_assign
CODE
style_id        mod_id
1               1
1               3
1               5
...
1               12
1               13  (3rd does have LS1/2 right?)
5               1
5               3
5               5
...
5               9
...
10              1
10              2
10              3
10              4
10              5
...




--------------------
Go to the top of the page
 
+Quote Post

4 Pages V   1 2 3 > » 
Reply to this topicStart new topic

 



RSS Lo-Fi Version Time is now: 23rd October 2019 - 09:35 PM