Crisis Of Design (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 11:16
Joined
Nov 28, 2005
Messages
2,466
I have added a ERD picture for this one.
Please note tbltracks in red.
My issue is the Track Title as I'm expecting the db to hold a large amount of data as the freeware project is being designed for use by DJ's I am trying to think of the best way to handle the track title.
there will be a import system which complicates things as I need it the not strangle the system.
I was thinking of adding an additional lookup table to hold the track title but it will create a lot of U.I problems not so much with the import system thats just a extra time thing, but the problem will come with manully adding records and tracks as there could be more tracks than a combo will display on lookup think thats something like 50k records.
Hope you can understand what I'm trying to explain, how would you handle the track title.



 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2013
Messages
16,553
think thats something like 50k record
it's 65k records..
How would I handle the track title? in the context of listing in a combo or listbox I would pre filter in some way - might be artist or year or album etc or simply 'starts with' a specific character
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:16
Joined
Feb 28, 2001
Messages
26,996
As far as track-titles are concerned, consider "cascading combo box" techniques. Dynamically build the WHERE clause for your track titles where your lookup would filter by artist, album name, record label, or any one of a dozen other possibilities in the music industry. Of course, it would start out as a huge multi-table join, but it wouldn't take long to narrow down your choices. I had a case when dealing with a hosting site database where the dynamic filters included make of computer, project name, operating system, and responsible team so that I could identify vulnerabilities. I used the cascading combo box method to change a table of about 500K records down to perhaps 20 records. Won't say it was pretty. But it was doable.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:16
Joined
Feb 19, 2002
Messages
42,970
You could also use a filtering technique that filters the RowSource after each character is typed. You can set it to start the filter after 3-4 characters to limit the rows returned if you need to. Or use something like this Address book example.
 

Attachments

  • AddressBook_20200813b.zip
    115.1 KB · Views: 169

Gasman

Enthusiastic Amateur
Local time
Today, 11:16
Joined
Sep 21, 2011
Messages
14,038
You could look at MajP's excellent classes?
 

Attachments

  • MajP FAYT V12.zip
    195.7 KB · Views: 174

isladogs

MVP / VIP
Local time
Today, 11:16
Joined
Jan 14, 2017
Messages
18,186
I also use cascading combos for such purposes. Even if you are far below the limit of 65,536, combos with hundreds of records are fairly unusable.

In one extreme case I use 5 of them to filter UK postcodes down from around 2.6 million to a single postcode by selecting area then district then sector and zone to reach the final list of postcodes for the selected zone.
If you want to see a cut down version of that example, look at http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723
 

Dreamweaver

Well-known member
Local time
Today, 11:16
Joined
Nov 28, 2005
Messages
2,466
You could also use a filtering technique that filters the RowSource after each character is typed. You can set it to start the filter after 3-4 characters to limit the rows returned if you need to. Or use something like this Address book example.
Thanks Pat but thats like my filter form example It wont work for what I need as I need to be able to add a recording track and select a track title from a lookup table.
 

Dreamweaver

Well-known member
Local time
Today, 11:16
Joined
Nov 28, 2005
Messages
2,466
This is something I Built 15 years ish ago The new project wont use this but it will show the entry system this uses a text field for the track title I am thinking of changing that to a lookup and/or search engine

 

Dreamweaver

Well-known member
Local time
Today, 11:16
Joined
Nov 28, 2005
Messages
2,466
it's 65k records..
How would I handle the track title? in the context of listing in a combo or listbox I would pre filter in some way - might be artist or year or album etc or simply 'starts with' a specific character
I just did somethng like that with my northwind Product And customers codes I.E the the code is made up of the first 3 letters of the first 2 words I think I'll add that to the track title lookup table So the track wind beneath my wings would have a code like WINBEN1234
 

Dreamweaver

Well-known member
Local time
Today, 11:16
Joined
Nov 28, 2005
Messages
2,466
this is my solution, I've only stated adding track but it seems to be working nicelly Hopefully the same can be said when I add the 30k records
The tables have a red border


The manual Entry Screen


I still have the Track Genre To Add to above screen the screen is an unbound form.
 

Users who are viewing this thread

Top Bottom