Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Dynamically adding fields to table and updating a form to include new field

    I've attached a diagram of my database structure. My issue is two fold. I need to be able to dynamically add new fields into the database attributeinputs table as product types diversify from the current product types.

    1) I can figure out how to dynamically add a new field to a table via php but ... what about a situation such as the voltage or body color (two examples).
    Right now, both have specific values that I need to control ie; Voltage can be 127v or 127-277v or 277v. Those are the only choices I want for each product and each product can only have one. Body color values are: bronze, black, white, gray or RAL. However, each product can have multiple choices of body color. How do I dynamically add that type of field into a database.
    I've manually set up a voltage and bodycolor table which will contain the specified values. The main table uses the foreign field to constrain the values that can be entered for a specific product. Does that makes sense? (I also understand I will probably need a junction table for the many-to-many bodycolor).

    Do I create the new table and then create the new field with the foreign key in the attributeinputs table to restrain the values for the new field?
    Or have I gone off the rails here?

    2) Assume also that I have a form that adds/updates a new product's data to the attributeinputs table. I would like to dynamically generate the form fields from the database attributeinputs table fields. How do I connect the field with any required values. Example below.

    ProductCode field is a standalone input field. That becomes a simple input tag.
    Voltage would be a select with options 127v or 127-277v or 277v.
    Bodycolor would be checkboxes with bronze, black, white, gray or RAL.

    Is there someway to use the database structure to have the php recognize that voltage/bodycolor in the attributeinputs table have a foreign key constraint and to look for the voltage or bodycolor table for the values?

    Hope these questions make sense. If I'm totally off base, let me know. Please don't tell me to use a framework that has already done this. This is a LEARNING project and I want to know how to do this. I'm not necessarily looking for code. Just for guidance about the approach.
    Attached Thumbnails Attached Thumbnails Dynamically adding fields to table and updating a form to include new field-af_diagram.jpg  

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    I need to be able to dynamically add new fields into the database attributeinputs table as product types diversify from the current product types.
    You should stop and rethink your approach. That statement tells me that the database was not properly designed.

    Modifying the schema dynamically (periodically) to add new fields just because product types diversify shows that the database is not normalized. It should be designed so that as product types diversify, you add new values, not new fields.

    Your jpg is low res and difficult to read, but it appears to me that the attributeinputs table is a catch-all table.

    Are you early enough in the project to redesign/normalize the database?

  • #3
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just started the process. I'm just starting trying to think through everything first. The attributesinputs is currently every feature/attribute type related to current products so I guess that would be a "catchall". I'm just a little confused though. Each product currently has a voltage (pick from predefined values), vendor (pick from predefined values), ballast (pick from predefined values), dimensions, bodycolor (multi pick from list), etc. Have you seen examples of databases that I could review to see how they handled this?

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    I'm on vacation so I don't have access to my work databases to be able to show you the EER diagram of my databases and I don't have enough info on what you're needing/doing so I can't say with certainty what changes you need to make, but I do see a couple things.

    The attributesinputs table has a voltageID and vendorID fields but they don't appear to be the fields used as the foreign keys of the corresponding tables. Likewise, that table also has a bodyColorID field set as the primary key rather than the foreign key linking the bodycolor table. I assume those foreign keys are the additional 3 fields not show in the diagram.

    If you change the relationship notation to "Connect to Columns" it will be easier to see the key relationships.

    You may want to reconsider your naming convention. CamelCase is common, but most often not easy to ready. Consider separating the words with an underscore. Which of these do you find easier to read?
    attributesinputs or attributes_inputs or possibly more simply attributes

    Are you familiar with the term "Database Normalization" and the steps needed to "normalize" the database? The tables on the left of the diagram looks to be a good start in the normalization process, but the other table may need refactoring.

  • #5
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes I'm familiar with normalization in a theoretical way. Part of my problem is I may not be using Workbench correctly to represent the intended constraints. I"m reworking the database schema based on your naming convention recommendations.
    The goal for the project: We are currently maintaining an increasingly complex "database" in excel worksheets. Each time we add in new products, we just add a column or create an entirely new spreadsheet for any new feature or group of products. To say it's getting out of hand is an understatement. I'm attempting to move to a real database.

    My base question about which I'm confused remains. How do I add in "attributes" for products as we add in new product types. Maybe I'm not asking the correct question.

    Here's an example of my problem simplifying to a smaller number of features and two product types.

    Example: the company initially sold just lighting fixtures The fixtures required features like voltage, wattage, ballast, mounting, reflector etc.
    Some of these features need to have values that don't deviate from a specific set.
    Let's use voltage as an example: voltage must be 127v, 277v or 127v-277v. I don't want someone inputting 127 or 127 volts or 127 voltage.
    Ballast is another. A product can only have a ballast such as CWA, Self Ballasted, Reactor NPF. I don't want someone inputting SELF or Self-Ballasted or Constant Wattage Autotransformer.
    So in the db, I had:
    table 1: product_code, voltage, wattage, ballast, mounting, reflector.
    Since I wanted to control voltage and ballast inputs, I had separate voltage and ballast tables with their associated values. The table1 voltage and ballast reference the voltage and ballast table to constraint their inputs.
    Example for the voltage table had voltage_id and voltage_name.
    1 127v
    2 277v
    3 127v-277v

    Now, the company decides to sell lamps-bulbs. (new product type). The new products have features such as beam angle, lamp efficacy base cap in addition to the voltage, wattage. They don't however need reflector or mounting. But now one of the new features has specific values such as GU5.3 and E26. This is my quandary. In the past, someone would have just added a new excel spreadsheet with the new products or added new columns to the existing spreadsheet.

    How do I model the database and allow the dynamic adding of these features?

  • #6
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    Do you have additional tables not shown in the diagram? I could be wrong, but it appears that you're designing your database structure around a single product type/category (i.e., lighting fixtures) and attributeinputs is the main table and everything else branches off of that table. When you need to add a new product type/category (lamps-bulbs), you start to see the problem that you're now asking about.

    You should have a "Products" table which has a limited number of fields such as ID, product_code (internal company code), UNC, item (name?), type, description, and product_details. The product_details field would be a foreign key referring to the attributes (or product_details) table. The details table could also be sub divided into additional linked tables. Instead of using a separate table for the voltage, you can define that field as a ENUM data type.

    Other tables you'll probably want/need to have would be inventory, sales_invoice, purchase_invoice, PO (purchase_order), customer, staff (employees). I could probably easily come up with a number of other required or useful tables. I suspect that your properly designed DB would have no less than 20 tables.

    The vendors table should not be directly linked to the attributes table. It should be linked to the PO and invoice tables.

    I don't know what you have in mind with that "group" table, but having field names such as "Category0", "Category1", etc is poor design. The field names should reflect/describe the type of data being held.

    Once all of the tables are setup properly, adding a new product would entail filling out a form that adds new values to the proper tables without having to change the schema to add new fields.

    I work in the IT dept of a large computer retailer and our buyers are constantly adding new products and product categories without needing us to modify the DB schema.

  • #7
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    "You should have a "Products" table which has a limited number of fields such as ID, product_code (internal company code), UNC, item (name?), type, description, and product_details. The product_details field would be a foreign key referring to the attributes (or product_details) table. The details table could also be sub divided into additional linked tables. Instead of using a separate table for the voltage, you can define that field as a ENUM data type."

    This is what I'm currently working toward. I just haven't had the chance as I was pulled onto another project.
    I've reduced my Products table to a few fields such as you described. I've been attempting to figure out a features table but I keep getting hung up on how to model the different types of fields and their differing requirements. This is my issue:

    1)I have features that would need only an input specific to a product
    Example: housing
    Will be Product Specific text - I don't have any predefined allowable values - This would just be text provided by the user.

    2) I have features that allow one choice but that choice must come from a specific group of values. This list of values may need to be updated by user in the future.
    Example: lamp
    Some current allowable values = Halogen, High Pressure Sodium (HPS), Incandescent, Induction, etc -

    3)I have features that allow multiple choices but all the choices must come from a specific group of values. This list of values may need to be updated by user in the future.
    Example: body_color
    choices allowed from the list for any particular product - Bronze, Black, White, Gray, RAL - Again, this list of values may need to be updated by the user in the future

    I thought the ENUM was interesting but was wondering if that was up-datable dynamically. Or should it be? What if the client needs to add in a new voltage?


    I've put up a jpg with more of the details. Hope it's clear. It looks fine on my computer.
    Dynamically adding fields to table and updating a form to include new field-build-up-imports_page_1.jpg

    "I don't know what you have in mind with that "group" table, but having field names such as "Category0", "Category1", etc is poor design. The field names should reflect/describe the type of data being held."
    Fully aware of that. This was just a temporary group. I should have deleted from the original image I uploaded.
    "I work in the IT dept of a large computer retailer and our buyers are constantly adding new products and product categories without needing us to modify the DB schema."
    That's what my goal is.
    Last edited by MTWKhome; 05-30-2014 at 01:00 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •