Huge database system

[George TOUBALIS]

Hi there

I need your advice I have a very serious problem.

I develop a huge music database system. A music statistic tool, A music marketing tool a logistic tool (all in one)

(not for home users but for a discography company's).

I wonder what can I do with the large amount of data and file limit size (2Gb) of file maker pro

Let's explain my self...

I have to store 100.000 ALBUMS (CD's Mc's DVD's etc) that means about 1.400.000 songs (tracks)

until now everything is ok but as I said previously the database is for a discography company ' s.

That means that I have to store and the Credits for every song (lyricist - composer - producer - guitar - sax - mastering etc .... about 400 different jobs).

Every song has about 12 credits minimum -35/50 credits maximum.

Marketing want to know amazing thinks

I want a list with songs who : is English , is remix, is Rock, is ballad, is duet, lyrics talk about friendship and has a licence to produce it in a various artists album

I want a list with songs who : is English , is live, is pop, 120 bpm, is instrumental, lyrics talk about love is depress and has a licence to produce it in a various artists album

Company's want to know everything, for example

" find the most player guitarist the last 3 years (with the song)"

OOPS

That means 100.000 ALBUMS has about 1.400.000 songs ids & about 22.400.000 Credits... (Credits_line_item_file)

Others line_item files are smaller (Album/song) 2.500.000 records (person/job) 600.000 records (person/communication) 400.000 records

and others even smaller

I design also a seperate cost database (mastering/sound/press/films/proofs/outdoor promotion/pays for everyone etc)

I think I have done a very good job for normalisation the data of my database 1NF ,2NF etc to minimise the duplication of data.

Total 70 files. 40 of them never opened, used only to receive/store data and 25 "user" files full of portals to manipulate data.

I was experiment with a filemaker file with 6 columns (fields) (Not empty) and every field (indexed) has about 10 - 25 letters

No duplications

5.750.000 record's. The size of the file was 750 Mb.

the search is really fast " Find name " George " resoults 4300 records in 1.5 Second

and portal too... From different file I type a keyword " George " and in the portal I have a list of 4300 records in 3 seconds

My baby is G4 450 mac 1024 MB Ram with Raid 0 SCSI disk system 6 disks ultra wide scsi 15.000 prm (I can duplicate a 1 Gb file in 11 seconds)

(The Raid was made for photoshoping)

I developed this database the last 1 1/2 Year

I have store 175.000 artists by sex, slyle, nationality, group or solo artist, job (Singer, composer etc)

I have store all music styles (did you know that there are 860 music styles out there??) by 10 major and 60 minor categories

I have store every job that is possible to do in a music or video production by 12 major and 25 minor categories

I have store every musical instrument ever existed (2500) by country, century, type etc

The user has to type only new songs and new persons Everything else is already stored. Everythig flows with keys not names

Every other information (jobs, instruments, track categorys etc is stored via portal/lists/buttons (of course the user can create a new category , subcategory etc every where in the database)

The Question:

I have about 4 Gb in excel files with all the information I need to import in my database (old archives from discography company)

I wonder what can I do if I want to store 500.000 or 1.000.000 ALBUMS, DVD's etc (224.000.000 Credits... in one Cretid_line_item_file (!))

Is it safe??

Can I split the file(s) in smaller pieces ?

But if one line item file was in separate files how the portals in other files (to display data) can work???

How can I search in separate files ?

I have almost finish with logical and physical analysis, keys, relationships, scripts , reports and the graphic user interface and now I have stacked

This huge project (I thing the biggest in my life) is a reason for me to change a database system???

Matt Petrowsky from filemakermagazine.com advice me that is time to move to "servoy"

I love file maker pro and I do not want to leave it.

Any other advice please???

George Toubalis

Art director

Sony Music Entertainment (Greece)

[email protected]

Ps.

Sorry again for my not so good English...

[Kurt Knippel]

Hmm, seriously I do not see any specific reason to move to an alternate RDBMS. Unless of course you simply cannot get past the 2GB file size limit.

Here is what I do when I get large files. I split them into two or more 1:1 files. For instance, I had a database where we stored information about kinds of Windows. In this we had various classification types (Fixed, Slider, Round, Square, ect) as well as both diagrams and photographic examples. It ended up being 500Mb or so. After using this, I found that when the classifications were needed, the loading of the photos slowed the system down. So I moved the diagrams and photos to a seperate file, which is only needed if someone is actually working in the WindowLibrary. Elsewhere in our systems only the WindowClassification db needs to be opened. It is something like 1.7MB.

Without seeing your schema, I cannot really recommend where and when to split data, but I would spend some time looking into this first. Your performance testing has shown that Filemaker can handle the task, and how do you know that the 2GB limit will always be around, maybe the next version will have a higher limit. :: ::

[stanley]

Georg:

I don't see the 2GB file limit causing you major headaches, if you divide the data logically. For example:

1. Albums.fp5 would have all the data on each album (title, artist, date, publisher, etc.)

2. Tracks.fp5 would have all the data on each individual track (title, length, date, artist credits, etc.)

3. Artists.fp5 would have all the data on each individual artist (sex, date of birth, etc.)

You could also divide the data deeper if need be, by introducing different files for different genres (or more logically, decades) so that you would have Tracks_60s.fp5 and Tracks_70s.fp5, etc.) So long as the relationships are solid, and you are able to link everything smoothly, you can certainly get around the file size limit. It's just a matter of imaginitive structuring.

-Stanley

[George TOUBALIS]

stanley said:

Georg:

You could also divide the data deeper if need be, by introducing different files for different genres (or more logically, decades) so that you would have Tracks_60s.fp5 and Tracks_70s.fp5, etc.) So long as the relationships are solid, and you are able to link everything smoothly, you can certainly get around the file size limit. It's just a matter of imaginative structuring.

-Stanley

Hi Stanley

First Normal form (1N )

Every member of the set depend on the key.

There are not repeating groups allowed.

A song may have 1-2-3.....singers

A song may have 1 or maybe 50 musical instruments

in the same song may are be 2 guitar players or 3 or 4...

The magic word in my database is "no limits"

I made a search engines using portals and multykeys everywhere....

I know that you can create a relationship to 1 file for 1 portal...

What do you mean "you would have Tracks_60s.fp5 and Tracks_70s.fp5, etc."

George

[Kurt Knippel]

Rather than having a single "Song" database, Stanley is suggesting that you seperate them out by decade or some other criteria, so that you have more smaller databases.

[George TOUBALIS]

Ok ...

lets say that I have a portal in A.fp5 file

and I want to view every song that contains the word "peace" from 1930 to 2003...

I think the idea of separate the song file (horizontal) don't work... except if have made 7 portals in A.fp5 file 1 for 30s, 1 for 40s, 1 for 50s.....etc and do 7 searches (I don't want that)

George

[Ugo DI LUCA]

George,

Not Horizontal separation, rather a Vertical Separation of the data, each record in that Line Item being divided, relocating some (or all) fields in separate files with the 1:1 relationship Kurt was referring to.

The best you could do at the moment is to have a look at the best explanation I could get about this approach, which is for sure what should be done here, when approaching 2Gb

See Article, from Ray (CobaltSky) on another Forum

and keep enjoying FileMaker...

[Anatoli]

Hmm, 2GB limit and 224.000.000 Credits... in one Cretid_line_item_file (!)

I will think about some MySQL stuff.

Although in plain search my FM is faster than MySQL. But in more complicated searches (queries) MySQL is much faster.

And MySQL is free and multiplatform.

[Mariano Peterson]

Follow Matt Petrowsky's advice and look into Servoy. It is an excellent product that removes many of FileMaker's problems; but yet remains very "FileMaker-esque". It is much more stable than FileMaker and allows you to use whatever SQL based database you want in the back end. BUT-- you don't have to know any SQL to program with Servoy (although you can use SQL if you like, which based on some of the questions you are receving may come in quite handy).

Servoy comes with a pretty good open source database, but you can always switch it to Oracle or SQL Server or MySQL if you want. Sometimes clients like to hear those names .