locked
Adding More Info to Table Rows RRS feed

  • Question

  • Hi, I need to design some data structure Ive never designed yet, so I need your help:

    The basic table lets say is about cars:

    Model Name ,Engine, Gear Box

    for this example we have these 2 rows:

    Model Name ,Engine, Gear Box

    ------------------------------------

    Fiat 500, 1.6 liter, automatic

    Fiat 500, 1.2 liter, manual

    I want my users to add to these exicting rows as many info as they wish..

    so a user can do this kind of action

    Fiat 500 (user added - bodystyle: hatchback) , 1.6 liter, automatic (user added - type:robotic)

    for each column the user can add information of any type..

    how do i need to design the tables?

    (important to say - the user cannot edit the original data, only add more layers to it...)

    Thanks!

     

    Friday, October 21, 2011 3:56 PM

Answers

  • You mention a "data structure", but as you've asked in the SQL Server database design forum, I'll assume you're talking about relational database tables.

    To provide users with the ability to add additional information about an existing entity, I suggest a two table approach. The first table (think of it as the parent table) defines the columns that are predetermined - Model Name Engine and Gearbox in your example. You'll need to add a column for a primary key, or determine the combination of columns that will make up the unique primary key for each row. Personally, I'd add an integer column, make it a sequence and use that as the primary key.

    The second table will hold the entries made by users (think of it as the child table), and each row will have a foreign key field to relate it to a row in the parent table. Because you want the users to be able to add anything they want, the only other column needed in this table will be a text field so users can enter whatever they want and have it stored as a string. I'd use a sequence column for the primary key in this table as well.

    The one to many relationship between the tables will allow for as many additional entries as desired against a given row in the parent table, and allow for easy retrieval of all entries associated with a particular parent row.

    As far as not allowing users to edit data in the parent table is concerned, simply design the application so users can see data in the parent table, but not edit it. Allow users to only enter data that will be stored in a new row in the child table.

     

    • Marked as answer by YardenST Friday, October 21, 2011 10:00 PM
    Friday, October 21, 2011 8:18 PM