Looks real good from I can see here. Sorry I'm so busy today or I might
have more time to look it over, but what I see while I wolf down my lunch
looks great!
Kit
-----Original Message-----
From: Ryan Honeyman [mailto:
honeyman@...]
Sent: Friday, March 17, 2000 1:44 PM
To:
eqbards@onelist.com
Subject: [eqbards] Song DB Model. (request for comments)
From: Ryan Honeyman <
honeyman@...>
I know there are a lot of technical people on this list.
You all have provided excellent feedback about numerous
things in the past, so I will post this here for discussion.
If you don't care to learn or know about database schemas
and such, go ahead and delete this post now, it's gonna get
really in depth. I'm trying to design a robust system to
search our knowledge base and dynamically generate our song
webpages. I'd also like to create an interface for those
of us who test regularly to update our data.
Rokenn knows that a while back I had dreams of porting all the
information we collected into a database. I started work on it
but to make it portable I decided against using our Oracle
installation here. Alternatively, I could have installed mySQL
and done it on that. However for flexibility I went with file
based information storage. It worked, but it sucked - needless
to say. I'm back in the db design phase now. We need to address
the elements of the songs we want to capture and make this whole
thing as flexible as possible.
If you know nothing about Entity Relationship, I fear this could
get confusing. Hey but if you are willing to learn, like a true
bard would, here ya go: (text for design, then maybe I won't be
so lazy and open my visual ER and make a PDF file or something) heh.
People can also suggest typing these variables too, as I haven't
done that yet.
Primary table =========================================================
How do we support quantified data? It varies per song.
songs
-----
song_id
song_level
song_name
song_effect
song_mana
song_duration
song_casting_time
song_cost
song_skill_id [fk song_skills]
song_req_inst
song_target_id [fk song_targets]
song_taunt_id [fk song_taunts]
song_formula
song_analysis_by
song_updated_by
Lookup tables ==========================================================
Really nice for controlling input with pulldowns. Standardizes data
as well and provides joining for complex queries.
song_skills (Percussion, Wind, Singing, String, Brass)
-----------
skill_id
skill_name
skill_desc
song_targets (Party, Non Party, Area Effect, Target, Self, etc)
------------
target_id
target_name
target_desc
guild_locations (Freeport, Qeynos, Kelethin)
---------------
location_id
location_name
location_desc
song_taunts (Nil, Low, Medium, High, Very High)
-----------
taunt_id
taunt_name
taunt_desc
comment_types (Comments, Strategy, Cautions)
-------------
comment_id
comment_name
comment_desc
Data mapping tables
=========================================================
song_locations (list of songs and locations, one to many relation)
--------------
song_id
location_id [fk guild_locations]
song_unstackables (list of songs which don't stack, one to many)
----------------- (this doesnt support 'other' comments, needs work here)
song_id
unstack_song_id
song_comments (list of comments by type)
-------------
song_id
comment_id [fk comment_types]
comment_desc
comment_author
============================================================================
=
Queries. [Note I am seeking input on creating this database, as such
no tables are out there as of yet. These statements may
be off a little, but the idea is there - correct SQL or
not ;) Just need to demonstrate the flexibility too. ]
o Where can I find the level <foo> song?
select s.song_level,s.song_name,gl.location_name
from songs as s,
song_locations as sl,
guild_locations as gl
where s.song_id = sl.song_id
and sl.location_id = gl.location_id
and s.song_level = <foo>
o What is the name of level <foo> song?
select song_level,song_name
from songs
where song_level = '<foo>'
o What type of type of target does Denon's Desparate Dirge use?
select s.song_name,st.target_name
from songs as s,
song_targets as st
where s.song_target_id = st.target_id
and s.song_name = 'Denon''s Desparate Dirge'
o What strategies are there for L50 Verses of Victory?
select s.song_name,sc.comment_author,sc.comment_desc
from songs as s,
song_comments as sc,
comment_types as ct
where s.song_id = sc.song_id
and sc.comment_id = ct.comment_id
and ct.comment_name = 'Strategy'
and s.song_level = '50'
etc, etc, etc.
Anyone care to comment? Additional values we should be storing?
Different model? Better implementation? Maybe take this to a
different area to talk about it, if a lot of people have ideas.
Harmonic.
+=================================+
| Ryan Honeyman, Programmer /
| Decipher, Web Group /
|
http://www.decipher.com /
+-----------------------------+
| phone> 757.664.1126 /
| pager> 757.629.1567 /
+==========================+
------------------------------------------------------------------------
PERFORM CPR ON YOUR APR!
Get a NextCard Visa, in 30 seconds! Get rates as low as
0.0% Intro or 9.9% Fixed APR and no hidden fees.
Apply NOW!
http://click.egroups.com/1/2121/7/_/451022/_/953325996/
------------------------------------------------------------------------
Please send submissions for the eqbards newsletter to
lol@...
with the subject submissions.