SQL Database

[mirhagk]

I want to store information in an SQL database. The information is links to videos (store along with some information like summary, rating etc) and a list of categories it belongs to. This could easily be all stored into one database, however I have an idea which I think will make searches faster.

Store the main database with basic information, then have a different table for each category, storing ids of the video in the main table. These would be very simple lists actually.

Now say I wanted to get a video that is a part of two different categories, which retrieval would be faster (i think the 2nd one would be) and which would be better overall

[jcollins1991]

In future be more careful about what you're calling a database and what you're calling a table as the two are completely different things. Your idea is interesting, but might be annoying to query and keep up to date.

Say you wanted to get all action and comedy movies (and return their basic info), you'd have to do something like:

SQL:

SELECT * FROM movies WHERE movie_id IN (SELECT movies_movieid FROM comedy_movies) OR movie_id IN (SELECT movies_movieid FROM action_movies)

It's a simple query, but if you expand it to 5 genres with overlapping movies you could be doing comparisons that aren't really necessary.

Also, if you wanted to figure out if any movies didn't have any genre related to it you'd have to query each of the tables, meaning you will also need to keep a list of tables you have genres for up to date for use in querying and you'd either have to check against it with each query if you let users choose genres (if the program has an interface someone will find a way around any front end checks you implement), or deal with errors from trying to access non-existent tables.

IMO for a simple system like this it'd be easiest to have a table with the basic information that also includes a list of keys referring to the genres a movie belongs to, something like "|1|2|5|8|", and a table containing a list of movie genres. Then with two queries you could do something like:

SQL:

SELECT * FROM genres WHERE genre_name IN ('Comedy', 'Action')

And if we find the keys are 1 and 4 we could do

SQL:

SELECT * FROM movies WHERE genres LIKE '%|1|%' AND genres LIKE '%|4|%'

It's not exactly the prettiest solution but it reduces the number of comparisons to the least necessary (I think) and should remove a few headaches from the error checking needed.

That's just what I think, I'm still relatively new to database stuff so I'm sure someone will correct me somehow XD.

edit: Didn't really explain my last SQL statement. The %'s are wildcards in SQL, so "%|1|%" will match up with something like "|1|2|5|8|", the |'s are just there as separators because otherwise after 9 you end up with stuff like 1011 if you didn't use separators, and it'd kill the system.

[rdrake]

Just keep three tables. One would contain all links to movies along with information, and the other will contain categories. You will also need a table to provide n:m mapping between movies and categories.

That is; movies have many categories, categories have many movies.

• Movies
  
• Categories
  
• MoviesToCategories
  

[jcollins1991]

Also, read up on denormalization... I think the approach I gave is half denormalized or something :S (got it from an example I saw awhile ago)... In smaller databases it doesn't really make a different how you store stuff (unless it's really messed up), but in larger databases different structures may outperform others... In the end though the best thing is take some of the possible approaches, set them up, and try running a bunch of queries and see which performs the best, as different environments will give different results for performance...

[rdrake]

Denormalization essentially deals with the efficient storage of information in a database. It also helps keep databases consistent. By having a denormalized database, you remove redundancy of data, reduce database size, and remove the need to perform updates in multiple places of the same thing.

Of course normalization absolutely kills performance as in order to get back your data, you typically must perform several more joins than you would have before. Joins are expensive.

[mirhagk]

I'm sorry but I'm still very new with all this SQL stuff so my questions are purely guesses.

The idea you proposed Jcollins is a pretty good idea, just two tables, one storing basic info + a string with the genre ids it has seperated with | |. Then a second table stating what each genre is? Is that right?

I'm going to have to read up on SQL calls and stuff, (not 100% what the like means) but it makes sense to me, thank you very much.

EDIT: Another question, I am assigning a entry in the table like so:
db.videos.AddObject(new video()
{
Category = category,
Name = name,
Description = descrip,
ImageURL = image,
ID=?
});

But i don't know what to do for the question mark. I left it out, but then it can only have one (since ID defaults to 0). How do I get ID to be a complelty knew ID?

[mirhagk]

Okay sorry for the double post but I have a serious problem now, while switching over to an online server, I have no idea how to actually make it connect to said server's database.

My current connection string is
metadata=res://*/Models.Videos.csdl|res://*/Models.Videos.ssdl|res://*/Models.Videos.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\moviess.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True"


and the one the online server says to use is
workstation id=moviess.mssql.somee.com;packet size=4096;user id=mirhagk2;pwd=[smart enough to block this out ];data source=moviess.mssql.somee.com;persist security info=False;initial catalog=moviess

i've tried replacing it, and merging at a couple spots but nothing works.

(When replacing it, it says workstation id keyword is not supported. It also says Packet Size keyword is not supported)

[jcollins1991]

Ya that's basically what I was saying. LIKE is a lot like = in SQL but allows you to add in wildcards so you get partial matches too (http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html). And you should be able to set the id column in your database to be the primary key and have the auto-increment attribute, so each time you add something it's 1 more than the last item inserted.

[mirhagk]

yes but how do I do that? And I already created the table, so preferebly without deleting and creating a new table, and can you help me with my second problem.

[DemonWasp]

Typically, auto-incrementing is specified with AUTO INCREMENT or SERIAL (which actually specifies something like INTEGER NOT NULL AUTO INCREMENT PRIMARY KEY).

To move existing data to a new table, first make the new table. Then, look at the SELECT INTO command, which lets you move data from one table to another, all within the database.