1. Fabrik 3.8 has been released. As usual, we strongly recommend that you backup your site (using Akeeba) before upgrading. Report any issues in the forums, we will answer promptly.

Understanding general concepts

Discussion in 'Standard Support' started by greif, Jul 27, 2011.

  1. greif

    greif New Member

    Level: Community
    Hi everybody, an absolute Fabrikar/database newbie here, looking for general help with understanding concept behind building a database with Fabrikar.

    I'm building a simple database for property evidence. I'll have let's say 2000 items in it, for each there's predefined list of elements to select from - ie. the office where it is in, the price range, kind of the item etc.

    What I did was I created a table with name PROPERTIES, I added dropdown elements to it - ie. OFFICE, KIND, PRICE RANGE etc -saved, and whoila, it works.

    But than I watched the tutorials on joining tables, and I'm not sure, if better approach would be to build a separate tables instead of the dropdown elements - ie. table OFFICES, KINDS etc. - and than join them together to build the form.

    Does this have some advantage over the dropdown elements? What is the rule to know when to create a separate table, and when to use just elements? Anyone could please describe to me the concept, when to use what?

  2. pfleischman

    pfleischman New Member

    Level: Community
    From my experience (which is basic like yours) I have separate tables when things need to have sub-atributes. Let me explain better:

    So say you have one table called: All_evidence_events
    Then you'd have a table called: evidence_items

    The evidence_events table would hold info like the who, what, when, where and why of the event with a unique identifier for each. The evidence_items table would hold individual rows of each piece of evidence associated with a particular event and would have a field called something like evidence_event_id which would match the unique ID of the All_evidence_events table. This would give you the ability to join the databases based on that common ID. If you need some more help, just shoot me a PM. I hope this helps!


  3. greif

    greif New Member

    Level: Community
    Thanks for reply, I'm not sure though I understand this. Let's stay with my concrete example:

    2000 items in the inventory list (ie. table, PC, lamp), each item has cca 20 "elements" (ie office, floor, price range) , each "element" some number of options to choose from (office 1-20, floor 1-5, price range 10-100,100-200 etc).

    What is the best way to build this table? The easy way is, at least as I can say, to create one table with field "name of the item", and all the elements as dropdowns.

    What is the other way? To have a table for each "element" = 20 tables, fill them with options and join all of them together somehow?

  4. pfleischman

    pfleischman New Member

    Level: Community
    Quick questions: are the options for each element static or always different?

    Create a new table (or if you have one just keep it) called something like 'inventory' then create multiple tables that link to that original table for the different sub options.

    So, you'd have the primary table called 'inventory' with the inventory's elements (table, PC, lamp) then you would have another table called 'office' that is linked to 'invintory' (see the attachment and how I picked 'posts' but yours would be called 'inventory' or whatever you decided on) then you'd create an element called 'office' as either a dropdown or a field. You'll notice that the element 'office' is now on both 'inventory' and 'office' tables. If you notice next to the element name is a little chain link, if you click on that you'll edit the element for every table instead of breaking the connection between tables. If you load the form for 'inventory' it will show all of the fields as well as the 'office' field to fill in.

    This works if you don't mind having every option on every screen, but if you want it to be unique that's a whole different logic.

    I really hope this helps, it's actually really hard to explain stuff like this!

    Attached Files:

  5. greif

    greif New Member

    Level: Community
    Thanks for trying to help me, really appreciate it!

    To answer your question, the options will be static - ie. we have offices 1-20.

    I tried your approach, and it works, but I still don't get, what is the advantage of doing it this way vs using one table with simple dropdowns vs using more tables and joins. Can you explain this, please?
  6. pfleischman

    pfleischman New Member

    Level: Community
    I think it all comes down to control, the way I told you to do it is easy. I think that if you wanted to have more complex filters and the ability to use the 'related data' feature and a lot more users and records in the database it would put a lot of pressure on just one table to store all of that info.
  7. greif

    greif New Member

    Level: Community
    Would better approach be using joins, instead of linking tables in the way you described (selecting the same db table)? I need to have database join anyway, to be able to use related data feature, right?

    I mean if the best way is to create separate tables for OFFICES, FLOORS etc., another table called INVENTORY, and than add one table that would glue them together by using join elements to each of the other tables?
  8. pfleischman

    pfleischman New Member

    Level: Community
    Well I like to use joins with separate tables because I like how you can click and have the linked data come up in a popup. I don't think you need a table exclusively dedicated to ID management just yet. I'd just have a main table with the top level item with a unique ID and then match that ID on the secondary item information tables. You actually can push those values via URL or Javascript. Check out my post I just put up about this (well it's not exactly about it, but you'll see how I changed a URL into java script)

    1 person likes this.

Share This Page