0

I have never used SQL. I would like to create a table with different columns. Then I would like to create a program which allows the user to insert entries. For each token of the entry, I would like to retrieve from an attribute of the corresponding column the way this particular part of the data entry should be entered. For example, I will have some data retrieved automatically (e.g. the date and time), some for which I will propose the user to insert the last added value, and some for which I will ask the user to explicitly type the value.

I do not know if "Attribute" is the right term; the attributes that I have available for each column at the moment are "Name", "DataType" and "Description"

I want that my program dynamically adapts to the table columns and to the property of each column, especially because I will have several tables to be filled. So my program would check the table columns with their properties, and interact with the user for the creation of a new entry.

Is there a possibility to add attributes to a column? Otherwise I will have to encode the information I need for my program in the "Description" field (aka "MS_Description").

I googled terms like "sql add column attribute", but I could only find results concerning how to add a column to a table.

Antonio
  • 19,451
  • 13
  • 99
  • 197
  • 1
    Maybe an SQL database is not the way to go with this kind of problem... Consider using a NoSQL database, like MongoDB – Barranka Sep 01 '15 at 21:00
  • 1
    What RDBMS are you using? Some DB's have meta built in like "Comments" on a field or table that you could use to store a description. All fields have a data type in nearly every major RDBMS, so you could just grab that from your front end. Likewise every field should have a name, so your pretty much set there. If your RDBMS doesn't have metadata built in where you can store description, then you'll need a seperate description table, but then you get into mixing objects and data and that's a pretty big anti-pattern red flag. – JNevill Sep 01 '15 at 21:01
  • @JNevill It should be Microsoft SQL Server. I have seen indeed there's a "Description" field. Are you suggesting to use that? – Antonio Sep 01 '15 at 21:10
  • You could use something like: http://stackoverflow.com/questions/1985254/sql-server-can-you-add-field-descriptions-in-create-table to set the description of the field. Then this is only dependent then on your ability to access this description, the field's data type, and field's name from whatever front end you are building. That should be relatively straight forward though. – JNevill Sep 01 '15 at 21:14
  • Thanks for the link! The path you suggest corresponds to the idea I had today. But looking deeper it seems I can avoid "hacking" the description: It seems what I am looking for is an "extended property" and I should be able to add it: https://msdn.microsoft.com/en-us/library/ms180047.aspx So, I could for example add an extended property that will encode how I should treat the data. And I could add, for example, also an extended property containing the question I should ask to the user and so on, and my program might use them to interact with the user and obtain the entry! – Antonio Sep 01 '15 at 21:23
  • It sounds to me like you're building an [entity-attribute-value](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) table, and you're now wondering where you should store the metadata for each entity or attribute of that table. I would store it in another table entirely rather than extended properties. That way you can store everything you want instead of jamming everything into the table or column properties. – Bacon Bits Sep 01 '15 at 21:31
  • @BaconBits One detail maybe important is that my attributes wouldn't be sparse, they would be always present for each entry. I see having a table for the column attributes as difficult to maintain, I hope I can avoid it. – Antonio Sep 01 '15 at 21:57
  • What data load do you expect? One of my projects had some quite similar challenges. I solved it with XML data and meta tables to describe the data as well as their appearance on the GUI. SQL Server can deal with XML really well! – Shnugo Sep 01 '15 at 22:56
  • @Shnugo It sounds really good! I think though for this project I will stick to the column attributes because I don't want to have to add accesses to an XML library into my C# project. I will instead retrieve *all* information I need from SQL calls. I have already seen that accessing column extended properties is a pain, I'll post the code when I get through them. – Antonio Sep 02 '15 at 10:03
  • @antonio, you wouldn't need anything special... First of all in my project I almost never have to deal with the XML directly. SQL Server will prepare resultsets the same as from tables. On the other side there is no need for any exotic XML library. DotNet contains XmlDocumnet, DataSet can write and read directly from an to XML - alltogether quite easy... – Shnugo Sep 02 '15 at 11:28

0 Answers0