Hi everyone, I have a project that I'm excited to undertake, but I am new to database design and would like someone to help me with some beginner logic. I am trying to create a database of products. I know that I can create 1 table called products, and have multiple columns holding information such as manufacturer name, application, material, etc. I want users, however, to be able to sort through the growing mountain of products in a logical way. For example: 3 drop-down menus and a search button. List 1: Narrows potential results by Application to Residential OR Commercial OR Industrial OR ALL List 2: Further narrows potential results by Use to Interior OR Exterior OR BOTH List 3: Is the available product categories (furniture, appliances, lighting, etc.) based on the previous 2 menu selections. Then...SEARCH!! Should application, product type, etc, all have their own tables? Can anyone point me towards some useful documentation on this subject? Cheers!
If you want to keep the database normalized and truly relational, you would put all of the columns related to the products in one table. If you have images or very large text being stored in the database you should put it in a separate table and query it directly to conserve overhead when searching the main table. If one column has one option out of a selection such as brand, you would reference the column to a brands table. If the column can have multiple options at the same time, such as Application by your example, you would make 2 tables, one with the options (Residential, Commercial, Industrial, etc...) and another table to reference to product to the options table. This allows for a single product to have multiple options at the same time. As far as the searching this is done much on the application level. Once you get the proper database structure, it's just a matter of developing the correct queries, and then applying the user input to those predefined queries. Just based on your references, I would do something like this. Products product_id product_title product_brand (references brad table) product_application etc... Brands brand_id brand_title Application application_id application_title Product_applications pa_id product_id (reference Products table) application_id (reference Application table)
Hi Jestep, Thanks alot for the reply, I appreciate it. The naming conventions for database columns are a little lost on me (i'm pretty green). Why do you provide a product_title and a product_id, brand_title and brand_id, etc? They way you've broken things down for me seems to make sense, though. I'm going to finalize what information I think needs to be provided and what relationships need to exist and will post again. Thanks again! EDIT: I've done some reading and now have a better understanding of what you explained above. I'm working on calling up my tables with some starting information in a .php file and will seek help when it's ready.
If you want to narrow the choice down by Application, Use and Categories, you'll have to have Application, Use and Category fields for each product. Index on each one separately, and all 3 together (2 indexes). I wouldn't use a manufacturer field, I'd use a manufacturer_ID field, and a Manufacturers table, listing name, address, phone, contact person, ID, etc.
Hi Rukbat, I've been brushing up on my PHP lately, and have neglected the database a little. I'm just beginning to familiarize myself with table relationships in MySQL Workbench, and indexes are still a little lost on me. Can you elaborate on what kind of indexes you are referring to? You responded just in time, actually. I have already set up my database somewhat as you have mentioned. I have an Application (commercial, residential, etc.) table, a Use (interior, exterior, etc.) table, and a product type (lighting, fixtures, wall finishes, etc.) table set up. All of them simply have an auto-increment ID column and a not-null name column. Additionally I have a large Manufacturer table which holds all the additional information (id, name, products offered, description, location, etc.) some of which I might make into additional tables, such as location, once I'm more confident about how it will all work. I've been wondering about how I will link them all together, but haven't had the time to get stuck into it. The other part I'm not sure about is the possibility of filing a product under several categories (for example, a material that is used both for wall and roof and foundation construction). I had anticipated the user having a series of check-boxes to select where product will go, but I have no clue how that would work in terms of the database. Thanks very much for the help, I'm still pretty green but I'll put whatever advice you can pass along to good use! Cheers!
Cheap and dirty - one record in the product table for each category (meaning that if they check 3 boxes, you do 3 inserts). You'll probably be searching by category, so one record for the product will come up when it's searched.
I think I understand...bear with me. Ie. Someone submits a manufacturer who makes roofing and flooring (I'm aiming more for people to upload companies rather than individual products). They select those two check-boxes (roofing and flooring) under "What does this manufacturer sell?". The manufacturer table (you said product, but I think we're on the same page) has columns for all available categories (ie. lighting, cabinetry, roofing, flooring, decking, etc, etc.), and can then be recalled using any of those. So, the company in the example would come up when the user queried 'flooring' because it would test positive for roofing and flooring but nothing else. Is that right? Sounds reasonable to me . What do you call a true or false field? I'm wondering if this is another way. I have no idea whether it's a worse or better option. What if I created a table for each of the searchable categories? Ie. a Lighting table with just two columns. When somebody selects Roofing AND Flooring (for example) the unique manufacturer_id would be inserted to all relevant tables. I have no clue how to do this yet with PHP, but it crossed my mind. Am I barking up the wrong tree? It sounds like I would be asking the database a lot of questions.... Thanks again for your help!
This thread can be closed. I've done my homework, clarified the questions I had previously and re-routed my project somewhat to accommodate the changes. Thanks for all the feedback.