Forum Home
Press F1
 
Thread ID: 121545 2011-10-30 22:54:00 Database table layout suggestions? Chilling_Silence (9) Press F1
Post ID Timestamp Content User
1240991 2011-10-30 22:54:00 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 (9)
1240992 2011-10-30 23:36:00 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. 		Chilling_Silence (9)
1240993 2011-10-31 01:52:00 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? 		HAL9000 (12736)
1240994 2011-10-31 02:36:00 Cool, that looks handy dandy... wratterus (105)
1240995 2011-10-31 02:46:00 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? 		Chilling_Silence (9)
1240996 2011-10-31 05:06:00 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. 		Paul.Cov (425)
1240997 2011-10-31 07:24:00 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 :-/ 		Chilling_Silence (9)
1240998 2011-10-31 07:46:00 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. 		8ftmetalhaed (14526)
1240999 2011-10-31 09:08:00 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. 		Erayd (23)
1241000 2011-10-31 09:41:00 Why do you want the lyrics in a separate table. mikebartnz (21)
1 2 3