PDA

View Full Version : Database table layout suggestions?



Chilling_Silence
31-10-2011, 10:54 AM
Hi all,

I'm starting a bit of a project (Mostly just to see if I *can* do it), basically it's a web-based lyrics presentation system for our church.

Currently we use desktop-based software, but I thought I'd try my hand at doing this from within a browser.

Aaaaanyways, here's what I'm thinking:
3 tables
1st called "Songs"
Contains:

songid (INT)
artistname varchar(50)
songtitle varchar(50)
ccli varchar(50)
datelastupdated (TIMESTAMP)
usetimes

The last two would be the date the song was last updated in the DB (Unsure if it'll be useful but it's no biggy for me to have that there so I figure I may as well for now) and the number of times that particular song has been used.
So, the 2nd table would be "songlyrics". It would contain:


songid (INT)
order (INT)
lyrics varchar(500)
The idea being that for each verse / chorus / whatever in a song, there's an entry with the matching songid number.

Lastly, I'd have another table, 'currentlydisplayed' with the details:


songid (INT)
lyricsplace (INT)

The idea being that whatever the 'songid' and 'lyricsplace' is currently set to, it gets those details from the songs and songlyrics tables, and displays them on-screen as applicable. Something like

"SELECT lyrics FROM songlyrics WHERE songid='songid' and order='lyricsplace'"

What I'm wondering is:
Is this the best way to do it?
Is there are more efficient way, considering I'll likely be updating / editing / deleting the occasional verse / chorus / bridge or whatever from a song?

Thanks


Chill.

Chilling_Silence
31-10-2011, 11:36 AM
Sorry I should have clarified.

The idea is that there is an admin or "control" screen that will allow the admin the eventually setup a schedule of the songs that will be used, and when they hit a button of some sort, said lyrics are set as "current" in that table, then all the client devices are updated.

HAL9000
31-10-2011, 01:52 PM
OK firstly fix the field "order". I try and avoid using field/table/db names that are SQL syntax words.

Your SQL query will probably need something like a nested "order by" songid then lyrics place to keep the song together and in the right order. I suspect you want this because some songs will only have 2 or 3 verses used.

Is varchar(500) going to be big enough for some verses. Perhaps a a Blob or Text field might be more appropriate here.

By the by, What about the chorus?

wratterus
31-10-2011, 02:36 PM
Cool, that looks handy dandy...

Chilling_Silence
31-10-2011, 02:46 PM
Order by, yes, that's a very good point!! Must have that in there! I'll rename it then so its not 'order', perhaps something like 'lyricsorder' should do.

Yeah 500 chars ought to be sufficient... Can always change it later if I find I'm getting too close to it :D

What about the Chorus? Have I missed something?

Paul.Cov
31-10-2011, 05:06 PM
Yeah, chorus may be a problem.

At times songs get a bit hacked about with regard to how many verses are sung, and howm many times the chorus gets looped through.
Ideally, a scheme that involves only one copy of the chorus for each song, but perhaps multiple 'skeletons' for each songs lines/verse/chorus structure so that the users can pre-select the long or short versions.

Chilling_Silence
31-10-2011, 07:24 PM
Any suggestions on doing something like that? :D

I figured I could always have an 'edit' function that allows me to specify which part of the song I want to edit, but I dunno, coz what I'm trying to figure out is a nice easy way to split things up on the screen.

Usually I only want say 4 lines on the screen at once, but there may be cases where a whole verse or song for example is only 6-8 lines, in which case I'd squeeze it all in on one go... Just wondering about how that kind of thing might work in practice, I'm having a difficult time visualizing it in my head :-/

8ftmetalhaed
31-10-2011, 07:46 PM
I had an exam on logical database design today. I'm expecting to fail.
I still don't have a mark back for the sql test I did half a semester ago, they didn't put them online.

I'd suggest something but I remember
SELECT*
FROM boring
WHERE sql = NOT NULL;

or something.

I just wanted to be included.

But in any case, what's the max length for a varchar?
If you wanted to you could perhaps split it up by verses, having extra bits for unique bits, and have a non sql based (or if it's possible, sql based, dunno, don't care at this point) engine that pulls up each verse in order and adds the song structure to it or something.
Like, you tell it verse 1 verse 2 verse3 x2 verse 4 verse 1, and it just spits out that at the top of the page while spitting out the verses in order or something.

God I hate sql.

Erayd
31-10-2011, 09:08 PM
But in any case, what's the max length for a varchar?For MySQL, 255 characters.

Chill, I'm in a bit of a hurry now, but happy to have a chat about your schema sometime tomorrow if that would be useful.

mikebartnz
31-10-2011, 09:41 PM
Why do you want the lyrics in a separate table.

Erayd
31-10-2011, 10:21 PM
Why do you want the lyrics in a separate table.The usual reason is to use something like FULLTEXT indexes on it, which are only supported by the MyISAM engine, while still maintaining the benefits of another engine (e.g. InnoDB, for proper transactions) for the rest of the database.

Another reason is to segregate management of bulk content from metadata.

Chill: Speaking of the lyrics table - you won't fit most songs into a VARCHAR, you should be using TEXT for that column.

mikebartnz
31-10-2011, 11:01 PM
The usual reason is to use something like FULLTEXT indexes on it, which are only supported by the MyISAM engine, while still maintaining the benefits of another engine (e.g. InnoDB, for proper transactions) for the rest of the database.

Another reason is to segregate management of bulk content from metadata.

Chill: Speaking of the lyrics table - you won't fit most songs into a VARCHAR, you should be using TEXT for that column.
Sounds fair enough to me.
I also thought text would be better.

Chilling_Silence
01-11-2011, 06:55 AM
Righto, that's cool then, can change it to varchar :) 255 chars is probably not *quite* enough.

mikebartnz, if you have a better way of storing all the lyrics, I'm all ears, but this certainly seems like the most ideal way of doing things. I want the ability to cram a whole lot of text in there, or have it spaced apart, so what I may end up doing for each "songid" is have a "starttags" and a "finishtags" column that I can put in the text height tags etc.

But yeah the goal is that basically I want to be able to split up the song lyrics and things so that only the relevant parts are displayed at any given point in time on-screen. The *eventual* plan being that I want to be able to have an administration / schedule screen for whoever is controlling the projector on the day, another 'page' that can be shown on Foldback screens, and another page that the main projector displays for the congregation or on mobile / tablet devices.
The split needs to occur in the lyrics because some songs have incredibly long chorus' / verses, whereas some songs are only 4-6 lines long in total.

8ftmetalhaed, thanks for that, I'm looking to do something like that for the "admin / schedule" screen.

donread
01-11-2011, 12:40 PM
Your "artist" should also be in it's own Table.

Chilling_Silence
01-11-2011, 12:56 PM
Whys that ?

Barnabas
01-11-2011, 01:02 PM
my guess would be because 1 artist can write many songs or a song can be written by many artists unless you are just having very large categories like hillsong or vineyard or something...

Chilling_Silence
01-11-2011, 01:06 PM
Ah, the idea is I just want the Artist for that particular song, so I'll store them for example like this:


| Song Name | Artist |

| Mighty To Save | Hillsong |
| Desert Song | Brooke Fraser |


I don't *think* it needs a table of its own?

Barnabas
01-11-2011, 01:18 PM
imo you can take it as far as you want. For example you could have an artists table, then a church table that an artist could belong to, then link these to song etc (google 1st, 2nd, 3rd normal form etc) but if you just want to keep it simple then sure, you dont have to have a separate table.

Do bare in mind though (and this is just and example) that Brooke Fraser also sings in some Hillsong songs, some United songs and by herself. If you want to have a record of all songs that Brooke sings in then yes, you should have a separate artist table, that way one song can have multiple artists.

Hmmm, not sure if I just confused the issue more or not :)

Chilling_Silence
01-11-2011, 01:24 PM
So this is what the 'songs' table would look like:


| songid | songname | artist | CCLI | lastused | songuse |

| 1 | Mighty To Save | Hillsong | 1234 | 20111031 | 5 |
| 2 | Desert Song | Brooke Fraser | 1176 | 20111029 | 3 |


The 'current' table would be:


| songid | currentslide | row | lastupdated |

| 2 | 1 | 1 | 2011-10-31 15:07:30 |

The row is the unique field, so what I've begun doing is:


SELECT * FROM current WHERE row='1'

And then from that I can establish what the current song and slide (part of the lyrics) should be displayed to the end user.

Lastly, the 'lyrics' table is what I'm not entirely sure about. Right now it looks like this:


| songid | lyricsid | lyrics | lastupdated |

| 2 | 1 | This is my prayer in the desert\nAnd all that's within me feels dry\nThis is my prayer in my hunger and need\nMy God is the God who provides | 2011-10-30 10:01:33 |
| 2 | 2 | And this is my prayer in the fire\nIn weakness or trial or pain\nThere is a faith proved\nOf more worth than gold\nSo refine me Lord through the flames | 2011-10-30 10:02:49 |

With the 'lyricsid' is used to determine what order things are displayed in the song, so things are sequential.

Chilling_Silence
01-11-2011, 01:28 PM
Do bare in mind though (and this is just and example) that Brooke Fraser also sings in some Hillsong songs, some United songs and by herself. If you want to have a record of all songs that Brooke sings in then yes, you should have a separate artist table, that way one song can have multiple artists.

Hmmm, not sure if I just confused the issue more or not :)
Yup I think you did ;-)

Nah but I see where you're coming from, and it's a good point.

I guess I should clarify further:
The use of that is purely to be displayed on the primary page, as I believe we're required to by the CCLI. So for example, the first part of the song is displayed, I'll be using the Artist / Song Title / CCLI down the bottom-right hand side in tiny writing. I *think* that's what we're required to do, but I'll follow up on that later.
As for searching, we'll just go by song name, although you do raise the very good point of alternative names. For example, some people know songs by their actual titles, whereas others know a song by the first line of the chorus, or the first line of the first verse ... So perhaps I should take that in to account and have an 'alternate title 1' and 'alternate title 2' field in the "songs" table...

Barnabas
01-11-2011, 01:46 PM
Info you need for ccli is here http://www.ccli.co.nz/faqs/faq.cfm?id=44.

Good thinking about song title and people knowing the same song by different names as its pretty common. Also bear in mind that some songs have the same title so having an artist table could be useful then as well. I once learnt the wrong song for Sunday morning because there were 2 songs called the same things and the worship leader assumed everyone would know which one she was talking about.

Anyway it may be best to wait for Erayd and see what he has to say as all of this stuff Im pulling from my days at uni which were sometime ago and I don't want to be pointing you in the wrong direction and it seems he does this stuff more often than I do. From what I can tell though you are on the right track.

Chilling_Silence
01-11-2011, 01:49 PM
Ah OK sweeeeeet, might add an extra field or two then to that table :)

I know what you mean, been there in that situation a couple of times ;)

Chilling_Silence
01-11-2011, 06:16 PM
OK so looking at using a single field for the lyrics and thinking of just doing away with the 'lyricsid' part altogether which means I'm probably able to get away with just two tables now, which is cool.



$result = mysql_query("SELECT * FROM lyrics WHERE songid='$songid' AND lyricsid='$lyricsid'");
while($row = mysql_fetch_array($result))
{
echo "SID: " . stripslashes($row['songid']) . "<br />LID: " . stripslashes($row['lyricsid']) . "<br />";
$brokenlyrics = explode("\n\r", $row['lyrics']);
}
echo "<hr /><pre>";
echo $brokenlyrics[0] . "\n";
echo $brokenlyrics[1] . "\n";
echo $brokenlyrics[2] . "\n";
echo $brokenlyrics[3] . "\n";
echo $brokenlyrics[4] . "\n";
echo $brokenlyrics[5] . "\n";
echo "</pre><hr />";


This means if I take something, such as the same lyrics from before:


Verse 1:
This is my prayer in the desert
And all that's within me feels dry
This is my prayer in my hunger and need
My God is the God who provides

Verse 2:
And this is my prayer in the fire
In weakness or trial or pain
There is a faith proved
Of more worth than gold
So refine me Lord through the flames

Means that the break between the choruses is now split. So, I guess what I've then got to store instead of a lyricsid is the applicable part of the array, for example I need to store "$brokenlyrics[1]" when I want it to display the second paragraph.
I think things are coming together ... :D

I've got some code which is refreshing the page every 2 seconds, which is crude but works for now :)

Might be worth popping it up on a Google Code project when I'm a bit further completed :D

somebody
01-11-2011, 07:50 PM
I hope you are doing something about the massive SQL-injection hole (it appears) you've got in that PHP code before you release it... not that it's going to be a particularly big target for attacks but still.

Chilling_Silence
01-11-2011, 09:12 PM
Yeah I just copied / pasted ^^ from notepad, not from the actual database...
I'm not sure if this is enough, but I'm doing:


$songid = $_POST[songid];
$lyrics = addslashes($_POST[lyrics]);
$sql="INSERT INTO lyrics (songid, lyrics) VALUES('$songid','$lyrics')";

mikebartnz
01-11-2011, 09:52 PM
mikebartnz, if you have a better way of storing all the lyrics, I'm all ears, but this certainly seems like the most ideal way of doing things. I want the ability to cram a whole lot of text in there, or have it spaced apart, so what I may end up doing for each "songid" is have a "starttags" and a "finishtags" column that I can put in the text height tags etc.
As Erayd said the only real reason for having the lyrics in a different table is to allow a text search. If you don't want that there is no point.

Chilling_Silence
02-11-2011, 07:25 AM
Can you explain a little about that? For example how it would differ? I don't think I understand ...