Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Sometimes this kind of problem is easier to visualize if you start off with the data model. If you want a setting per row, then two tables would probably be the best way to store this:</p> <p>Site:</p> <pre> SiteId SiteKey SiteName 1 XYGZ4345 Client Site 1 2 AZT43752 Client Site 2 </pre> <p>This would define the list of sites you have config for. I'd use a SiteKey as you'd put this in your web.config and it's better to abstract this away into a random string or GUID (to make it harder to accidentally load someone else's config), the client can change their name and you don't get confused in the future as you didn't use their old name as a key etc etc.</p> <p>The config table itself is also simple, if we treat every setting as a string:</p> <p>SiteSetting:</p> <pre> SettingId SiteId SettingName SettingValue 1 1 Brand KrustyBrand 2 1 GoogleId MSFTSUX0R 3 2 Brand ConfigMasters(TM) </pre> <p>You can then load all the config quite simply:</p> <pre><code>SELECT * FROM SiteSetting INNER JOIN Site ON (SiteSetting.SiteId = Site.SiteId) WHERE Site.SiteKey = 'XYGZ4345' </code></pre> <p>Now we have a list of key value pairs you could then store in a class like:</p> <pre><code>public class SiteSetting { public Site Site { get; set; //Site would just be a simple class consisiting of Id, Key and Name to match the database table } protected Dictionary&lt;String, String&gt; Settings { get; set; } //Simple key value pairs } </code></pre> <p>So this is a very simple solution. However, we can take it further - things to consider:</p> <p>1) Can we add an environment to this somewhere?</p> <p>We could either add a site per environment</p> <p>OR</p> <p>Add an environment to the SiteSetting table. The advantage of this is that you could define enironment = 'ALL' to avoid duplication. </p> <p>OR</p> <p>The database the configuration is loaded from defines the environment; so you change the config connection string in the app config. Of course, to connect to a different environment you have to change app.config, but you would potentially have to do that anyway to change the client key and/or environment.</p> <p>2) Add the concept of user defineable settings - some settings you are going to want to change, some you are going to want to lock. A bit column containing "UserDefinable" would allow you to sort this out</p> <p>3) Typing of settings. </p> <p>This is slightly more difficult. You might have something like:</p> <pre> PropertyId PropertyName PropertyType Format UserDefined 1 Brand String NULL 1 2 DatePresentation DateTime "yyyy-MM-dd" 1 </pre> <p>The Settings table then only defines a value, and a PropertyId. The advantage of this is that you can then start to increase the information about each setting you are storing, and reuse this information as the design is more normalized. The Settings class then changes like so:</p> <pre><code>public List&lt;PropertyValue&gt; { get; set; } //replacing the dictionary </code></pre> <p>PropertyValue then looks something like:</p> <pre><code>public class PropertyValue { int Id { get; set; } public string Name { get; set; } public string Value { get; set; } public string PVType { get; set; } //Could be an enum public string DisplayFormat { get; set; private string _RawValue; public string Value{ get{ switch(PVType){ case "DateTime": return Convert.ToDateTime(_RawValue).ToString(DisplayFormat); break; case "Double": return Convert.ToDouble(_RawValue).ToString(DisplayFormat); break; default: return _RawValue; } } set{ _RawValue = value; } } } </code></pre> <p>Things like the Value method need to be improved to support robust error handling (you could also investigate using Convert.ChangeType to simplify the switch block)</p> <p>This topic is as simple or as complicated as you choose to make it ;-) </p> <p><strong>Editing</strong></p> <p>As regards maintaining them; a very simple GUI would allow the user to see all of their properties in a tabular format. You might consider having rows where UserDefinable = 0 as readonly, otherwise the user can edit and add rows. You'd need to validate, especially for duplicate Setting Names for example.</p> <p>The easiest way to do this is to use the DataGrid; a simple mockup might look something like this:</p> <p><img src="https://i.stack.imgur.com/XL4kQ.png" alt="Mockup of Grid Edit"></p> <p>And a more sophisticated approach might look something like <a href="http://trirand.com/blog/jqgrid/jqgrid.html" rel="nofollow noreferrer">this</a></p> <p>Generating the form is therefore as simple as databinding a collection of PropertyValue objects to your chosen grid solution.</p>
    singulars
    1. This table or related slice is empty.
    1. POManaging Dynamic Website Settings Persisted in a Database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USStijn
    1. USdash
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POManaging Dynamic Website Settings Persisted in a Database
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThis is my favorite answer so far and has some great ideas to improve the existing architecture (especially the site+sitesetting table and marking a property as user-editable or not), however my main sticking point is how I generate the edit form for these.
      singulars
      1. PO
      1. USTreborbob
    2. CO@Treborbob I'd use a data grid with editing. Stuff with UserDefinable = 0 would be locked, you can add new rows, and you have to validate for duplicate property value names. In effect, you are creating an interface that represents a view across those tables in the database. The basic approach is very simple; http://www.4guysfromrolla.com/articles/071002-1.aspx
      singulars
      1. PO
      1. USdash
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload