Ander,

I'm not really sure how seeing the UI makes it easier to provide examples of working DB design, but this is our existing front end:

https://apps.library.albany.edu/dbfinder/

Some of the limitations of the current front end are because the backend DB is only partially relational with duplicated information throughout.  There are a number of exceptions and logical manipulation of query results hardcoded into the app to deal with the deficiencies of the DB.  It works, but barely.

The new DB will be hosted in MariaDB, and if designed well, should make the app code clean and efficient.  From the smidgen of DBA instruction I do have, I know 80% of any DB project should be in the design.  As I said though, there are multiple many-to-many relationships and we aren't sure how to design that well.  Most subjects have sub-topics and most subjects (and subtopics) have one specialist, but could have more.  Specialists have multiple subjects and sub-topics, of course.  Resources can fall into multiple subjects and sub-topics and may be ranked for their usefulness within a subject or sub-topic.  Resources may also have multiple types (i.e. images, video, dictionaries, newspapers, etc.), and multiple resources will have the same type.

Hope that is what you are looking for.

Thanks,
Erich


On Thursday, August 25, 2022 at 00:28, Akierig  eloquently inscribed:

> Howdy Erich!
> 
> A link to the thing you're trying to replace and a few words about what works
> best for you would be helpful.
> 
> cheers,
> 
> ander kierig
> 
> On 2022-08-24 at 20:45 (+00:00) Hammer, Erich F <[log in to unmask]>
> wrote:
>> Hi,
>> 
>> (I'm relatively new to the library world, so please excuse any
>> ignorance I may display here.)
>> 
>> We have a (poorly constructed) legacy database that holds/connects our
> myriad of online resources to subjects and specialists.  The webpage front
> end is our "DBfinder" to assist patrons in identifying where they might find
> good information on a topic of interest and which subject librarian/specialist
> would be most likely able to provide further assistance.  It gives us (and
> subject librarians) the ability to highlight better resources while keeping the
> interface consistent for all of it (which our LibGuides is not very good at given
> the wide disparity in subject librarian technical and presentation skills).
>> 
>> We are trying/hoping to design a much better relational database for this
> that will make the code and logic behind the web application and front-end
> display much more efficient and organized.  None of us are DBAs, so I am
> nervous as we delve into designing/building something and
> identifying/discussing all the many-to-many relationships that our efforts
> may end up also poorly designed (although it's hard to imagine it would be
> worse).  Thus I thought it would be worth asking about.
>> 
>> Does anyone here has something similar (a "DBfinder") that is working
> (well) for them?  Would you be willing to share the relationship
> diagram/structure of the DB so we can think about what works for someone
> else (and maybe base ours off of some of it)?
>> 
>> Thanks,
>> Erich
>> 
>> 
>> --
>> Erich Hammer            Head of Library Systems
>> [log in to unmask]         University Libraries
>> 518-442-3891              University @ Albany
>> 
>> "Without doubt you are not sane."    -- Tage Danielsson
>>