How to best manage and store a simple database

[nineclicks]

I'd like to have a database of filesnames. Basically I want to be able to add a filename to the database, add some options to it and a list of tags. Then be able to either locate a filename and get it's options (such as rotation or custom thumbnail file) and tags. OR be able to search for tags and get all the filenames that have a certain tag.

My first thought was to simply use a text or csv file and just use basic string searches and stuff but there will likely be up to thousands of files so if there is something more efficient that might be better. What should I look in to?

Thanks

 

[jmcilhinney]

You might be able to get away without a database but no developer can get by without knowing how to work with databases for too long so this would probably be a good chance to learn how. SQL Server CE might be a good choice in this case. You can add a Local Database item to your project and then add the tables in VS. You might like to follow the Data Walkthroughs link in my signature for some examples.

 

[IanRyder]

Hi,

The perfect solution... (comments removed since jmcilhinney has already touched on this)

Another option, and possibly a much simpler and quicker option, could be to look into using an XML file. An XML file is a structured file which can easily be read from and written to via a DataTable or DataSet within VB.NET.

Hope that helps.

Cheers.,

Ian

 

[jmcilhinney]

Hi,

The perfect solution... (comments removed since jmcilhinney has already touched on this)

Another option, and possibly a much simpler and quicker option, could be to look into using an XML file. An XML file is a structured file which can easily be read from and written to via a DataTable or DataSet within VB.NET.

Hope that helps.

Cheers.,

Ian

I agree with Ian that an XML file mapped to a DataSet would probably be the quickest and possibly easiest option for this particular project. I guess it's a matter of what's your priority: getting this project done as easily as possible or learning something that you'll almost certainly need to use in some future projects.

 

[nineclicks]

I think I'll just go with XML. I have a question about the dataset though. How would I best deal with having a varying number of tags for each filename? Should I just put all of the tags in one cell and separate them in it? I plan on doing a lot of reverse-searching for tags.

 

[jmcilhinney]

I think I'll just go with XML. I have a question about the dataset though. How would I best deal with having a varying number of tags for each filename? Should I just put all of the tags in one cell and separate them in it? I plan on doing a lot of reverse-searching for tags.

You should create a File table with FileId and FileName columns.

For the tags, you have two choices. You could use a 1:many relationship or a many:many. For 1:many, you would create a Tag table with TagId, FileId and TagName columns. Each Tag that relates to a File will have that File's FileId in its FileId column. In that case you will end up with duplicates in the Tag table.

For many:many, you would create a Tag table with TagId and TagName columns and then a FileTag table with FileId and TagId columns. When adding a tag to a file, you would first insert the tag into the Tag table if it doesn't exist. You would then insert a record into the FileTag table with the FileId of the file and the TagId of the tag. In that case you will not have any duplicates in the Tag table.

 

[nineclicks]

I read that about 10 times trying to completely understand it but I don't quite get the many:many idea.

Please correct me if I am wrong but this is how I am picturing a 1:many table:

red	Magenta
red	Yellow
red	White
green	Cyan
green	Yellow
green	White
blue	Magenta
blue	Cyan
blue	White

With each primary color being like a tag and the mixed color being like the file, and there being an entry for each tag for each file. Have I got that right?

I read the many:many description 10 more times and I still don't understand how it works or why it would mean no duplicates.

 

[jmcilhinney]

1:many means that every one File has many Tags. Many:many means that every one File has many Tags and every one Tag has many Files. A many:many relationship requires a third table. 1:many might look like this:

[B]FileId   FileName[/B]
1        File1
2        File2
3        File3

[B]TagId   TagName   FileId[/B]
1       Tag1      1
2       Tag2      1
3       Tag3      2
4       Tag4      2
5       Tag5      3
6       Tag6      3

So FileId 1 is related to TagId 1 and 2, FileId 2 is related to TagId 3 and 4, FileId 3 is related to TagId 5 and 6. Each File is related to (potentially) many Tags but each Tag is only ever related to one File. Many:many might look like this:

[B]FileId   FileName[/B]
1        File1
2        File2
3        File3

[B]TagId   TagName[/B]
1       Tag1
2       Tag2
3       Tag3

[B]FileId   TagId[/B]
1        1
1        2
2        2
2        3
3        3
3        1

So FileId 1 is related to TagId 1 and 2, FileId 2 is related to TagId 2 and 3, FileId 3 is related to TagId 3 and 1, TagId 1 is related to FileId 1 and 3, TagId 2 is related to FileId 1 and 2, TagId 3 is related to FileId 2 and 3. Each File is related to two Tags and each Tag is related to two Files.

 

[IanRyder]

Hi,

Gosh, just spent ages drawing these two images so I am going to post anyway. Says exactly the same thing at jmcilhinney has just posted but in a pictorial way:-

1 to Many Relationship (Note the duplication of the mixed colours):-



Many to Many Relationship (Notice no duplication of mixed colours) :-



Hope that helps,

Cheers,

Ian

 

[nineclicks]

Ok I think I get it. So the point is that I have no duplicate filenames or tags. And all the duplicates are just the file and tag id's. And likely faster searching. Is that correct?

For id's I'm okay just using sequential numbering right?

Sent from my SAMSUNG-SGH-I337 using Tapatalk 4 Beta

 

[IanRyder]

Hi,

Ok I think I get it. So the point is that I have no duplicate filenames or tags. And all the duplicates are just the file and tag id's.

Using the Many to Many relationship principle, Yes.

And likely faster searching. Is that correct?

Without doing some testing, and depending on the number of records you are talking about, then I would suggest that this would depend on how you implement your search criteria.

For id's I'm okay just using sequential numbering right?

Yes, just make sure that the ID's remain unique in the FileName and TagName DataTables in the Many to Many scenario and the ID in the FileName DataTable in the One to Many scenario.

Cheers,

Ian

 

[jmcilhinney]

For id's I'm okay just using sequential numbering right?

The DataTable can generate the IDs for you. Set the AutoIncrement property to True, the AutoIncrementSeed to 1 and the AutoIncrementStep to 1. That's basically equivalent to a SQL Server identity or an Access AutoNumber.

 

[nineclicks]

I'm slowly figuring out the dataset. Should I be using relations to connect the third table to the other two?

 

[jmcilhinney]

I'm slowly figuring out the dataset. Should I be using relations to connect the third table to the other two?

Yes you should.