Using a list box with MySQL

[paintacar]

Hello, I have been gone quite a while from starting out using Alpha I never actually finished any apps yet, a couple years back I was trying to learn how to use Alpha and MySQL and SQL Server, the partner I had lost interest in our project. I am back with several new ideas that I believe can be accomplished using Alpha, I know I will have to upgrade to get the features I will be needing. So you know I am still new to this and realized that I had to learn how a Database should be structured and relationships work, so last fall I took a class on Database administration and design so that I had a better understanding. I am not an advanced user so please take it easy on me.

Now what I am trying to accomplish is....
I want to use a cascading or dropdown listbox type of entry, to for example- add items to inventory. My question is do I make a separate table that is used to supply the dropdown or cascading boxes with the values that are then saved to the inventory item's own table?
One reason I ask this is I believe that it would be then easy for me to make a separate form type way to make changes and manage or add fields to the supplying values?

Or would I be best off to make a table with all of the category choices and it supplies the cascading(dropdown) values and just saves the inventory items number in the category table as a foreign key?

or should all the choices be stored in the listbox itself and then saved to the database in the inventory item's table or a separate category table?

I am curious how others have done this, please don't over "technitalk" me suggestions because I may get lost in your explanation.

Thanks for any input in advance.
Jerrod

[Keith Hubert]

Re: Using a list box with MySQL

Hi Jerrod,

I realise that your question are interlinked but it makes it easier to answer and others to follow if you keep it simple and ask one specific question at a time.

I want to use a cascading or dropdown listbox type of entry, to for example- add items to inventory. My question is do I make a separate table that is used to supply the dropdown or cascading boxes with the values that are then saved to the inventory item's own table?

Answer, sort of but it all depends what you want to populate the Inventory table with.

[paintacar]

Re: Using a list box with MySQL

I will want to be able to search the inventory for items based on the category in the end. So I would like for the category to be saved in the database and linked to items that are in each specific category. I just listed any possible ways that I had thought of it could be done.

[Keith Hubert]

Re: Using a list box with MySQL

Jerrod,

Sounds like you need a separate table for category.

[paintacar]

Re: Using a list box with MySQL

So then I would just add the itemid field to the category table as a foreign key? Correct? Meaning itemid is the primary key used for the items in my item table.

[Keith Hubert]

Re: Using a list box with MySQL

Hi Jerrod,

The way I would do it, is in your Inventory table you have a category field. This would be a lookup to the Category table. You can populate this field with the description of the category or the category_id. The main reason to capture the category_id is to use as a link to the Category table in a View.

[Michael1954]

Re: Using a list box with MySQL

Hi Jerrod

There are 2 ways to approach; both have advantages and disadvantages.
You can take a column in a table and do "Distinct" (Get one record for every new "Distinct" word or phrase or number date) This ensures that all choices are available, but only choices that are currently in the table. (Alpha does this for you). If you build a second table you may get problems keeping Category_Id up to date. Obviously the way around this is to restrict entries to the first table based upon what is available in the category table.

As you get more confident it is possible to update both tables at the same time.

Really you need to decide on your table structure based upon what data you are trying to store.
Read a simple article on database "Normalisation".

I always spend a little time thinking how I will gather the information, what existing processes exist. I try to change as little as possible, in the existing non computerised data entry, but use the database to enforce business logic.

Michael

[paintacar]

Re: Using a list box with MySQL

Keith, So you are suggesting using a bridge table to link the items to the category using itemid and categoryid. Which are the primary keys for each. By the way I think I Should have posted this in the web based since that is how I will run this app.

[Keith Hubert]

Re: Using a list box with MySQL

Hi Jerrod,

I thought to start with this could be web as you mentioned cascading dropdown.

Cascading Dropdowns are used for filtering records, not for data entry.

I'm not suggesting a link table. Each inventory product would be given a category, this would be got by a lookup to category table. If you just capture the Category_Id then you would need to create a View linking on that field. You could capture the text of the category into your inventory table in which case you would not need the View.

If you would like to call me on Skype, I might be able to help you better.

PS. Next time use the area of the forum that will give you the most appropriate help. (Sorry if that sounds a bit formal).