New to Ninox - How to create and link a "lookup" table?
I'm creating a Media Collection database consisting of 3 main tables; one for LPs (albums), another for CDs, and a third for DVDs/Blu-ray Discs (movies). I would like to create a few shared "lookup" tables to be utlized by my three main tables. Ex. Create and maintain a table for "Artists" - that would be referenced by the LPs and CDs tables. Create an "Actors" table that would be referenced by the movies table. Create a format lookup (LP, CD, DVD, BD, etc.) that could be referenced by all three tables (Disc Type). Obvioulsy, there could be others as well, such as an MPA rating table (G, PG, PG-13, R, and so on). But once I understand the concept, I should be able to replicate the results as needed.
Also, is there a way to create a new "Artist", "Actor" and so (if not found in their repsective tables) directly from the one of the main tables w/o having to add it via the reference table?
Hope this request makes sense. As I mentioned, barnd-new to Ninox, so forgive me for the Level 1 questions and any misuse of proper terminology.
4 replies
-
Hi there -
May I suggest that you not create three tables for what is basically similiar data (disc name, production date, artist, etc). Maybe create a table called media that would have a record for each type of media (LP,CD,DVD).
Then you would create one table called artists. I would say a musician and actor are people and you would track the same info about them (i.e. First Name, Last Name, Birthdate, etc). The only difference would be tracked in a field again called type which would be a multi-choice field as the same person could be an actor and musician, i.e. Ice-T.
To link the two tables, you would create a new reference field in your media table to your artists table. Now when you create a new record in media you would then select an artists and now you have linked the artist to a record in your media table. You will see when you click on artists that you can then add a new artist if they aren't already in your artist table.
Just some thoughts. Good luck.
-
Thanks Fred! I will certainly give that a try. The only reason I was thinking of creating three separate tables is because while many of the values are similar in nature and can be shared as you suggest, there are still quite a few that are specfific based on media type. What would really be nice is if the form fields would change dynamically based on the enetered media type. For instance "Colored Vinyl" (Y/N) doesn't apply to CDs and DVDs. An MPA No. or Rating doesn't apply to CDs and LPs, etc. Perhaps that's doable, but I'm just starting out, so I have a lot to learn.
Thanks again for your prompt reply. Looking forward to digging in...
-
Re-reading your response Fred. I see what you're saying now.
-
That is true that there is data that is specific to the type of media. I was just thinking ahead that you may want to add a new field in the future and you don't have to do it to three different tables. If you look at the more option part of a field there is a Display field only, if. So you can have fields show/hide depending on media type.
I've redesigned my main db many times over as I learned, so I hope I can save you some redesigns.
Content aside
- 3 yrs agoLast active
- 4Replies
- 612Views