Base n:m many to many relationships between two tables using intermediate table, how to enter, display & modify the data using forms?
File upload: Many_to_Many_Concept.odb
The uploaded file is a simple example of 2 tables in a many to many relationship using an intermediate table. The Items table represents pieces of metal art. The Colors table represents paint colors that can be applied to the various Items. All colors are NOT applied to all Items. There is relevant data in all 3 tables. I've completed the Tools/Relationships chart accurately.
The goals:
1) When entering a new Item, be able to see and select the paint color(s) - by color name (not just the ID).
2) When adding a new Color, be able to see and select the Item(s) by Item Description (not just the ID).
3) Be able to modify any Item record adding or removing colors.
4) When viewing Item records, see the color names displayed on the form.
I suppose I'm no longer a newbie to LO base but I'm no expert either. Any advice would be greatly appreciated.
Thank you in advance!
Hello,
Have previously looked at your web site & so have a tiny bit of understanding at to your project here. Having a difficult time placing what you want into the overall picture. For example, when Item 0 (Bird - large) is ordered, a) is that available in only red, blue and orange?; b) how is it known what color is wanted - seems no tie to order process? What am I missing? These three tables seem isolated from all else with the exception of an item (of what color is unknown) to be ordered.
Hi - I appreciate you checking out our web-site though it is not the eCommerce site we want it to be. I hope you followed the link to our etsy store where you can better understand the breath of our metal art inventory. The example file I uploaded is just a microcosm of the larger system, in my attempt to not "muddy the water" with a lot of other superfluous minutiae. Your questions are good. The system is not part of a customer ordering system. It is part of a Sales Order Tracking, Inventory and Costing system. I'm trying to build the Sales Order Tracking piece first, since that is the urgent need. The Item file is the "heart" file (in my mind) of the system. Each piece of metal art has attributes associated with it that are used to analyze sales and forecast production. The many to many relationship ...(more)
Part 2 - My Wife and I are the only users of the system I'm creating. We are constantly thinking of new metal art to make and offer. Those new pieces (new Items, in the example uploaded file) would be assigned various "colors" (think "tags") that would then be used for sorting, grouping, filtering, etc. for future reports (that I have yet to begin creating). We occasionally add new tags (colors) but often add new Items (and their related "tags"). Today, we track everything in an incredibly complex (my opinion) Calc spreadsheet that is cumbersome to maintain and VERY inflexible. Let me know if you have more questions, PLEASE!
Part 3 - I haven't specifically answered your questions - I'm sorry. Here goes. a) Item 0 (Bird - large) would only be available in Red, Blue and Orange - Correct. b) The system would not be interacting with potential customers to be concerned about a different color request. I hope this helps.
One quick question. I know you've tagged questions with HSQLDB. Are you using the default embedded HSQLDB? IF so, before you go too far, consider changing. It is not for production use as there can be potential data loss. Backup!
Thank you for bringing up HSQLDB. I'm using the internal HSQLDB as a development platform and for learning. I have a MySQL server sitting across the room, that I'll move to for multi-user access (Wife & I) once I get the system working well enough to use. I see you've posted an answer, I'm about to look at it now.
Consider moving to MySQL now (I personally find PostgreSQL and Firebird to be better). You can place MySQL server software on any machine and migrate to another machine later. There are multiple reasons for changing NOW. Already mentioned embedded problem with data loss. Also, that embedded version is VERY old. Moving to MySQL or another DB allows you to take advantage of newer versions of SQL and capabilities. Later conversion may require you to re-write SQL you may already have as syntax may differ. Field types in tables can differ also. Well this is some. Hopefully you get the point.
Okay, you've made an impression. Will I have to add a connector to my workstation Base install to connect to say a Firebird instance, also installed on my workstation?
Well, first thing is to determine which DB you are to end up with. I believe I mentioned planning before. Consider in this choice the future and how e-commerce may affect this as hosting companies may allow for different DB's (they host but you interface with) and this may be a factor in choosing. Migration and connection will depend upon the from and to and is best in a new question.
Not going to migrate this system outside our house.
Only going by you mention of:
Not to say your entire system but the possibility of data between eCommerce & your system. Again, just planning & future consideration.
In answer to your question about connectors, there are some answers here in the forum. You can also check Chapter 2 - Creating a Database, section on Accessing external databases in the Base documentation.