Can it be done?

[sleeper43]

Hi gang, I have mastered open office calc, now I am on to base. I am brand new and know nothing about base. My question is, can I create tables within tables, a sort of recursion?

I want to start a new project to make a database for my video collection that I can organize and browse. Here is the basic format I hope to achieve:

Main database

Table one
---------
Movie Sets
Individual Movies
Television Shows

TABLE WITHIN MOVIE SETS
-----------------------
0-9
A
B
C
...
X
Y
Z

TABLE WITHIN INDIVIDUAL MOVIES
------------------------------
0-9
A
B
C
...
X
Y
Z

TABLE WITHIN TELEVISION
-----------------------
0-9
A
B
C
...
X
Y
Z

TABLE WITHIN MOVIE SETS A
-------------------
Movie Title
movie image (it will be a JPG file)

FORM WITHIN TITLE
-----------------
Title
Rating
Year
Aspect
Description
Notes

These are just some examples for the form off the top of my head, I would likely want more than just those, such as one for a clickable URL that links to a preview of said title on youtube, and a few other plain text entries.

Basically (pun intended), I would like to create a data base for easy browsing of my collection when I want to watch a movie, but am not sure which I want to watch (similar to how you would browse titles on netflix). Is it possible? Any suggestions? Are there any templates out there in cyberspace that I could tweak to accomplish this?

Thank you in advance!

PS, BONUS POINTS! If you can also include a way to have an entry that is an active link to the video on my hard disk that will open it in the default media player, for example, C:/users/admin/videos/great movie.mp4 since I have ripped all of my DVDs and Bluray to mp4 files to store on external hard disk drives, its so much more convenient than digging through a mountain of disks.

[RoryOF]

A hyperlink to the video file from an OpenOffice application should cause the video to open in the default video player, using that application's default options. One can also edit the hyperlink to point to a video player, with command line parameter to the required video, using other display parameters such as full screen.

[sleeper43]

Thanks RoryOF, bonus points for you! That also answers the question about linking to a youtube video for a preview. I did it in calc and it worked just fine. Now if I can get an idea about how to set up my tables and structure, I will be ready to build my DB!

[RoryOF]

I can't help on databases, but I believe "Base Tutorial: From Newbie to Advocate in a one, two... three!" by Mariano Casanova
Base Tutorial: From Newbie to Advocate in a one, two... three!
is a good starting point for general database design.

[Villeroy]

viewtopic.php?f=13&t=79079
The exact usage depends on the exact details where your movies are stored and how to use simple tools in creative ways.

[sleeper43]

well the document Rory shared is extremely complicated, I am pretty sure it vastly overcomplicates the app.

Really, the structure I want isn't very complicated. It will be structured as follows:

Table
|
----------------------
| | |
Table Table Table
| | |
27 tables under each of the above, and then a varying number of tables under each of those 26, and a form at the end of each structure with a record for a video title.

[Villeroy]

I don't see any structure anywhere. All I know is that OpenOffice forms provide everything necessary to edit 1:1, 1:n and m:n relations. There are macro solutions for the missing hyperlink fields.

[sleeper43]

shoot the post messed up the formatting. Its basically one primary table, there will be three subtables beyond that that say movie sets, individual movies, and television. Each subtable will have 27 subtables of its own A - Z and one for numbers. Each of those will have a varying amount of subtables beneath them. A tree structure really.

[eremmel]

The best advice I can give you is start with Rory's document. From your statement I conclude that you have misconception about how to setup a database. It looks like you want to map N-tree to table structure. Don't try that with relational databases.

[UnklDonald418]

You won't find instant gratification in Base. There is a learning curve and Mario Casanova's tutorial can lessen that curve. To someone new to Base it may seem “extremely complicated “ but it presents the essentials of good database design, things you need to know. The organization you describe in your posts would be inefficient not to mention difficult to create and maintain.
It is a common mistake for someone new to Base to begin designing tables right away. You can save a lot of angst and frustration if you follow the design path laid out in the tutorial. You will likely use the GUI to enter the design of your tables so you don't probably don't need to spend a great deal of time on using SQL to generate your tables, but you should at least have a feel for it. You never know when that knowledge will bail you out.
UML diagrams are not essential but they can sure come in handy when things get confusing.
Learning to use the principals of normalization he presents will allow you to build an efficient and versatile database. As you are reading the material on design, think about how it applies to your situation. You may even find yourself drawing some sort of diagram to keep those thoughts organized.
If you won't be generating hard copy then you can probably skip the discussions on Reports.
In order to design Base tables you need to know what information you have and what questions you want answered.
Alphabetizing titles is an easy task for a query. Once you understand the basics of relational database design you may find that you may not need different tables for Films and TV shows. It isn't likely you will need 27 tables, in fact your application could end up with only 5 or 6.
Besides alphabetic how you want to be able to lookup titles
by series (James Bond, Star Wars ...),
by type (Drama,Comedy,Documentary ...)
by star (Sandra Bullock, Sean Connery …)
some other attribute(s)
The tutorial is a good place to learn the power of SQL queries and filters.
Once you get to Form design you will find List and Combo boxes will be very helpful for data entry, and SubForms for displaying lists of attributes, such a the stars of a particular Film or TV show.

Just looking at what you have presented you might end up with a Main Table with columns for
ID(primary key), Title, rating, year, description, episode if it's part of a series, series name, format (TV, Film), Location in your library (shelf or drawer), image, URL of preview, awards, and any other unique attributes.
You might have another table for types
ID(primary key), Foreign Key( the ID from the Main Table), type Name (Comedy,Drama …)
this would allow the show to have more than one type (Romance and Comedy and Action).
Then a table for Stars
ID(primary key), Star Name, Birth Date, Photo and any other personal information for this star.
Another table with star/show information
ID(primary key), Foreign Key( the ID from the Main Table), Foreign Key( the ID from the Stars Table), awards for this show, any other information you might want that is particular to this star in this show. This will allow the show to have any number of stars associated with it.
I'm sure there will be more, but maybe this gives you a sample of how your tables might look, and how they could relate to one another.

[Villeroy]

For anybody who is familiar with any of the standard conformant, SQL driven databases of the past 3 decades, Base is a brain dead simple frontend without surprising features. It's just the bare minimum of what you can expect. Nothing complicated at all.
For anybody else, Base is a no go.

[sleeper43]

Thanks guys, I will do homework

[RoryOF]

I would suggest doing an Internet search to see if you can find a published Video database using SQL and/or Base.

[sleeper43]

I would suggest doing an Internet search to see if you can find a published Video database using SQL and/or Base.

Thanks for the suggestion, I found a free app called Ant Movie Catalog that does most of the things I wanted, it also does a few things I like that I had nothing thought of. I will just use that app.