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
1241001	2011-10-31 10:21:00	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.	Erayd (23)
1241002	2011-10-31 11:01:00	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.	mikebartnz (21)
1241003	2011-10-31 18:55:00	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.	Chilling_Silence (9)
1241004	2011-11-01 00:40:00	Your "artist" should also be in it's own Table.	donread (6401)
1241005	2011-11-01 00:56:00	Whys that ?	Chilling_Silence (9)
1241006	2011-11-01 01:02:00	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...	Barnabas (4562)
1241007	2011-11-01 01:06:00	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?	Chilling_Silence (9)
1241008	2011-11-01 01:18:00	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 :)	Barnabas (4562)
1241009	2011-11-01 01:24:00	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 (9)
1241010	2011-11-01 01:28:00	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...	Chilling_Silence (9)
1 2 3