Say I had some data, for which I want to fit a parametrized model over it. My goal is to find the best value for this model parameter.

I&#39;m doing model selection using a [AIC][1]/[BIC][2]/[MDL][3] type of criterion which rewards models with low error but also penalizes models with high complexity (we&#39;re seeking the simplest yet most convincing explanation for this data so to speak, a la [Occam&#39;s razor][4]).

Following the above, this is an example of the sort of things I get for three different criteria (two are to be minimized, and one to be maximized):

![aic-bic][5]
![fit][6]

Visually you can easily see the elbow shape and you would pick a value for the parameter somewhere in that region.
The problem is that I&#39;m doing do this for large number of experiments and I need a way to find this value without intervention.

My first intuition was to try to draw a line at 45 degrees angle from the corner and keep moving it until it intersect the curve, but that&#39;s easier said than done :) Also it can miss the region of interest if the curve is somewhat skewed.

Any thoughts on how to implement this, or better ideas?

Here&#39;s the samples needed to reproduce one of the plots above:

    curve = [8.4663 8.3457 5.4507 5.3275 4.8305 4.7895 4.6889 4.6833 4.6819 4.6542 4.6501 4.6287 4.6162 4.585 4.5535 4.5134 4.474 4.4089 4.3797 4.3494 4.3268 4.3218 4.3206 4.3206 4.3203 4.2975 4.2864 4.2821 4.2544 4.2288 4.2281 4.2265 4.2226 4.2206 4.2146 4.2144 4.2114 4.1923 4.19 4.1894 4.1785 4.178 4.1694 4.1694 4.1694 4.1556 4.1498 4.1498 4.1357 4.1222 4.1222 4.1217 4.1192 4.1178 4.1139 4.1135 4.1125 4.1035 4.1025 4.1023 4.0971 4.0969 4.0915 4.0915 4.0914 4.0836 4.0804 4.0803 4.0722 4.065 4.065 4.0649 4.0644 4.0637 4.0616 4.0616 4.061 4.0572 4.0563 4.056 4.0545 4.0545 4.0522 4.0519 4.0514 4.0484 4.0467 4.0463 4.0422 4.0392 4.0388 4.0385 4.0385 4.0383 4.038 4.0379 4.0375 4.0364 4.0353 4.0344];
    plot(1:100, curve)

---

## EDIT

I accepted the solution given by [Jonas][7]. Basically, for each point `p` on the curve, we find the one with the maximum distance `d` given by:

![point-line-distance][8]


  [1]: http://en.wikipedia.org/wiki/Akaike_information_criterion
  [2]: http://en.wikipedia.org/wiki/Bayesian_information_criterion
  [3]: http://en.wikipedia.org/wiki/Minimum_description_length
  [4]: http://en.wikipedia.org/wiki/Occam%27s_Razor
  [5]: http://i.stack.imgur.com/iBgo3.png
  [6]: http://i.stack.imgur.com/AG52Y.png
  [7]: https://stackoverflow.com/questions/2018178/finding-the-best-trade-off-point-on-a-curve/2022348#2022348
  [8]: http://i.stack.imgur.com/IoRbN.png

Finding the best trade-off point on a curve

Our database is designed based on EAV (Entity-Attribute-Value) model. Those who have worked with EAV models know all the crap that comes with for the purpose of flexibility.

I asked my client about the reasons why using EAV model (flexibility), and their response was: Their entities change over time. So, today they may have a table with a few attributes, but in a month time, a few new attributes may be added, or an existing attribute may be renamed. They need to produce reports to get back to any stage in time and query the data based on the shape of entities at that stage. 

I understand this is not feasible with a conventional relational model, but I personally see EAV as anti-pattern. Are there any other alternative models that enables us to capture the time dimension in changes to the entities and instances? 

Cheers,
Mosh

Alternatives to Entity-Attribute-Value (EAV)?

I have users who can login on a front-end page, and admins who can login on an admin page.

Should both users and admins be &quot;Users&quot; with different roles, or should they be split in different tables?

Is it good database design to have admin users in the same table as front-end users?

I&#39;m building a group calendar application that needs to support recurring events, but all the solutions I&#39;ve come up with to handle these events seem like a hack. I can limit how far ahead one can look, and then generate all the events at once. Or I can store the events as repeating and dynamically display them when one looks ahead on the calendar, but I&#39;ll have to convert them to a normal event if someone wants to change the details on a particular instance of the event.

I&#39;m sure there&#39;s a better way to do this, but I haven&#39;t found it yet. What&#39;s the best way to model recurring events, where you can change details of or delete particular event instances?

(I&#39;m using Ruby, but please don&#39;t let that constrain your answer. If there&#39;s a Ruby-specific library or something, though, that&#39;s good to know.)

What&#39;s the best way to model recurring events in a calendar application?

What&#39;s the longest possible worldwide phone number I should consider in SQL `varchar(length)` for phone.

considerations:

 - \+ for country code
 - \() for area code
 - x + 6 numbers for Extension extension (so make it 8 {space})
 - spaces between groups (i.e. in American phones +x xxx xxx xxxx = 3 spaces)
 - here is where I need your help, I want it to be worldwide


Consider that in my particular case now, I don&#39;t need cards etc. number begins with country code and ends with the extension, no Fax/Phone etc. comments, nor calling card stuff needed.



What&#39;s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone

I haven&#39;t been able to fully grasp the differences. Can you describe both concepts and use real world examples?

What&#39;s the difference between identifying and non-identifying relationships?

One of the first things I think about when using a new service (such as a non-RDBMS data store or a message queue) is: &quot;How should I structure my data?&quot;.

I&#39;ve read and watched some introductory materials. In particular, take, for example, [Kafka: a Distributed Messaging System for Log Processing][sw], which writes:

* &quot;a Topic is the container with which messages are associated&quot;
* &quot;the smallest unit of parallelism is the partition of a topic. This implies that all messages that ... belong to a particular partition of a topic will be consumed by a consumer in a consumer group.&quot;

Knowing this, what would be a good example that illustrates how to use topics and partitions? When should something be a topic? When should something be a partition?

[sw]: http://www.systemswemake.com/papers/kafka

As an example, let&#39;s say my (Clojure) data looks like:

    {:user-id 101 :viewed &quot;/page1.html&quot; :at #inst &quot;2013-04-12T23:20:50.22Z&quot;}
    {:user-id 102 :viewed &quot;/page2.html&quot; :at #inst &quot;2013-04-12T23:20:55.50Z&quot;}

Should the topic be based on `user-id`? `viewed`? `at`? What about the partition?

How do I decide?

Data Modeling with Kafka? Topics and Partitions

**Let&#39;s say I have two tables:**

    Table: Color
    Columns: Id, ColorName, ColorCode

    Table: Shape
    Columns: Id, ShapeName, VertexList

**What should I call the table that maps color to shape?**

    Table: ???
    Columns: ColorId, ShapeId

What should I name a table that maps two tables together?

I am implementing a tagging system on my website similar to one stackoverflow uses, my question is - what is the most effective way to store tags so that they may be searched and filtered?

My idea is this:

    Table: Items
    Columns: Item_ID, Title, Content

    Table: Tags
    Columns: Title, Item_ID

Is this too slow? Is there a better way?




What is the most efficient way to store tags in a database?

Are soft deletes a good idea or a bad idea?

Instead of actually deleting a record in your database, you would just flag it as `IsDeleted = true`, and upon recovery of the record you could just flag it as `False`.

Is this a good idea?

Is it a better idea to physically delete the record, then move it to an archive database, and if the user wants the record back, then software will look for the record in the archive and recreate it?

Are soft deletes a good idea?

Using this modified example from [the Rails guides][1], how does one model a relational &quot;has_many :through&quot; association using mongoid?

The challenge is that mongoid does not support has_many :through as ActiveRecord does.

    # doctor checking out patient
    class Physician &lt; ActiveRecord::Base
      has_many :appointments
      has_many :patients, :through =&gt; :appointments
      has_many :meeting_notes, :through =&gt; :appointments
    end
    
    # notes taken during the appointment
    class MeetingNote &lt; ActiveRecord::Base
      has_many :appointments
      has_many :patients, :through =&gt; :appointments
      has_many :physicians, :through =&gt; :appointments
    end
    
    # the patient
    class Patient &lt; ActiveRecord::Base
      has_many :appointments
      has_many :physicians, :through =&gt; :appointments
      has_many :meeting_notes, :through =&gt; :appointments
    end
     
    # the appointment
    class Appointment &lt; ActiveRecord::Base
      belongs_to :physician
      belongs_to :patient
      belongs_to :meeting_note
      # has timestamp attribute
    end


  [1]: http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

How to implement has_many :through relationships with Mongoid and mongodb?

It is crucial for my application to be able to select multiple documents at random from a collection in firebase.

Since there is no native function built in to Firebase (that I know of) to achieve a query that does just this, my first thought was to use query cursors to select a random start and end index provided that I have the number of documents in the collection.

This approach would work but only in a limited fashion since every document would be served up in sequence with its neighboring documents every time; however, if I was able to select a document by its index in its parent collection I could achieve a random document query but the problem is I can&#39;t find any documentation that describes how you can do this or even if you can do this.

Here&#39;s what I&#39;d like to be able to do, consider the following firestore schema:

    root/
      posts/
         docA
         docB
         docC
         docD

Then in my client (I&#39;m in a Swift environment) I&#39;d like to write a query that can do this:

    db.collection(&quot;posts&quot;)[0, 1, 3] // would return: docA, docB, docD

Is there anyway I can do something along the lines of this? Or, is there a different way I can select random documents in a similar fashion?

Please help.


Firestore: How to get random documents in a collection

We develop a commercial application. Our customers are asking for custom fields support. For instance, they want to add a field to the Customer form.

What are the known design patterns to store the field values and the meta-data about the fields?

I see these options for now:

**Option 1**: Add Field1, Field2, Field3, Field4 columns of type varchar to my Customer table.

**Option 2**: Add a single column of type XML in the customer table and store the custom fields&#39; values in xml.

**Option 3**: Add a CustomerCustomFieldValue table with a column of type varchar and store values in that column. That table would also have a CustomerID, a CustomFieldID.

    CustomerID,  CustomFieldID, Value
    10001,       1001,          &#39;02/12/2009 8:00 AM&#39;
    10001,       1002,          &#39;18.26&#39;
    10002,       1001,          &#39;01/12/2009 8:00 AM&#39;
    10002,       1002,          &#39;50.26&#39;

CustomFieldID would be an ID from another table called CustomField with these columns: CustomFieldID, FieldName, FieldValueTypeID.

**Option 4**: Add a CustomerCustomFieldValue table with a column of each possible value type and store values in the right column. Similar to #3 but field values are stored using a strongly-type column.

    CustomerID,  CustomFieldID, DateValue,           StringValue,       NumericValue                 
    10001,       1001,          02/12/2009 8:00 AM,  null,              null
    10001,       1002,          null,                null,              18.26
    10002,       1001,          01/12/2009 8:00 AM,  null,              null
    10002,       1002,          null,                null,              50.26


**Option 5**: Options 3 and 4 use a table specific to a single concept (Customer). Our clients are asking for custom field in other forms as well. Should we instead have a system-wide custom field storage system? So instead of having multiple tables such as CustomerCustomFieldValue, EmployeeCustomFieldValue, InvoiceCustomFieldValue, we would have a single table named CustomFieldValue? Although it seems more elegant to me, wouldn&#39;t that cause a performance bottleneck?

Have you used any of those approaches? Were you successful? What approach would you select?
Do you know any other approach that I should consider?

Also, my clients want the custom field to be able to refer to data in other tables. For instance a client might want to add a &quot;Favorite Payment Method&quot; field to the Customer. Payment methods are defined elsewhere in the system. That brings the subject of &quot;foreign keys&quot; in the picture. Should I try to create constraints to ensure that values stored in the custom field tables are valid values?

Thanks

======================

**EDIT 07-27-2009:**

Thank you for your answers. It seems like the list of approaches is now quite comprehensive. I have selected the option 2 (a single XML column). It was the easiest to implement for now. I will probably have to refractor to a more strongly-defined approach as my requirements will get more complex and as the number of custom fields to support will get larger.

What are design patterns to support custom fields in an application?

What is the difference between 

ONE: [![One][1]][1]

ONE AND ONLY ONE: [![One And Only One][2]][2]


  [1]: http://i.stack.imgur.com/GdODV.png
  [2]: http://i.stack.imgur.com/ll1yQ.png

Crow&#39;s Feet - One vs One And Only One

Reading several papers and documents on internet, I found many contradictory information about the Cassandra data model. There are many which identify it as a column oriented database, other as a row-oriented and then who define it as a hybrid way of both.

According to what I know about how Cassandra stores file, it uses the *-Index.db  file to access at the right position of the *-Data.db file where it is stored the bloom filter, column index and then the columns of the required row.

In my opinion, this is strictly row-oriented. Is there something I&#39;m missing?

Why many refer to Cassandra as a Column oriented database?

Let&#39;s say we have a root collection named &#39;todos&#39;.

Every document in this collection has:

 1. `title`: String
 2. subcollection named `todo_items`
 
Every document in the subcollection `todo_items` has

 1. `title`: String
 2. `completed`: Boolean

I know that querying in Cloud Firestore is shallow by default, which is great, but is there a way to query the `todos` and get results that include the subcollection `todo_items` automatically?

In other words, how do I make the following query include the `todo_items` subcollection?

    db.collection(&#39;todos&#39;).onSnapshot((snapshot) =&gt; {
      snapshot.docChanges.forEach((change) =&gt; {
        // ...
      });
    });

Cloud Firestore deep get with subcollection

I&#39;ve been looking into [Datomic][1], and it looks really interesting. But while there seems to be [very good information on how Datomic works technically][2], I have not seen much on how one should think about data modeling.

What are some best practices for data modeling in Datomic? Are there any good resources on the subject?

  [1]: http://datomic.com/
  [2]: http://docs.datomic.com/tutorial.html

Data modeling in Datomic

I always thought that databases should be denormalized for read performance, as it is done for OLAP database design, and not exaggerated much further 3NF for OLTP design.  
 
PerformanceDBA in various posts, for ex.,  in [Performance of different aproaches to time-based data][1] defends the paradigm that database should be always well-designed by normalization to 5NF and 6NF (Normal Form).  

Have I understood it correctly (and what had I understood correctly)? 

What&#39;s wrong with the traditional denormalization approach/paradigm design of OLAP databases (below 3NF) and the advice that 3NF is enough for most practical cases of OLTP databases?  

For example:   

  - [&quot;The simple truth ... is that 6NF, executed properly, is the data warehouse&quot; (PerformanceDBA)][2]    
 
I should confess that I could never grasp the theories that denormalization facilitates read performance. Can anybody give me references with good logical explanations of this and of the contrary beliefs?

What are sources to which I can refer when trying to convince my stakeholders that OLAP/Data Warehousing databases should be normalized?   

To improve visibility I copied here from comments:  

&gt; &quot;It would be nice if participants would
&gt; add (disclose) how many real-life (no
&gt; science projects included)
&gt; data-warehouse implementations in 6NF
&gt; they have seen or participated in.
&gt; Kind of a quick-pool. Me = 0.&quot; – Damir
&gt; Sudarevic

[Wikipedia&#39;s Data Warehouse article](http://en.wikipedia.org/wiki/Data_Warehouse) tells:  

&gt; &quot;The normalized approach [vs. dimensional one by Ralph Kimball], also
&gt; called the **3NF model** (Third Normal Form) whose supporters are
&gt; referred to as “Inmonites”, believe in Bill Inmon&#39;s approach in which
&gt; it is stated that the data warehouse should be modeled using an E-R
&gt; model/normalized model.&quot;

It looks like the normalized data warehousing approach (by Bill Inmon) is perceived as not exceeding 3NF (?)

I just want to understand what is the origin of the myth (or ubiquitous axiomatic belief) that data warehousing/OLAP is synonym of denormalization?  

Damir Sudarevic answered that they are well-paved approach. Let me return to the question: Why is denormalization believed to facilitate reading?   


  [1]: https://stackoverflow.com/questions/4375192/performance-of-different-aproaches-to-time-based-data/4390401#4390401
  [2]: https://stackoverflow.com/questions/4264947/what-is-best-practice-for-representing-time-intervals-in-a-data-warehouse/4290190#4290190

Should OLAP databases be denormalized for read performance?

Someone told me that it was bad design to have loops in the datamodel. I have heard this before a couple of times but didn&#39;t pay much attention. For example you have entities User, Project, Activity. A project is owned by a User, so we have a one-to-many relationship from user to Project. An activity can be assigned to a single User, another one-to-many relationship from User to Activity. Of course a project is defined by a set of activities, another one-to-many relationship from Project to Activity. Thus a loop is formed.

I asked this guy why is it bad design but he told me he didn&#39;t know either, he was told so too, monkey learning at it&#39;s best.

I tried searching but I guess I didn&#39;t use the proper words, however this seems  to me something that should be fundamental for someone trying to design a DB.

So, can anyone point me to some useful info about loops/cycles in er/db diagrams, should they be avoided?






Why should I avoid loops when designing relationships for a database?

I&#39;ve heard of a few ways to implement tagging; using a mapping table between TagID and ItemID (makes sense to me, but does it scale?), adding a fixed number of possible TagID columns to ItemID (seems like a bad idea), Keeping tags in a text column that&#39;s comma separated (sounds crazy but could work). I&#39;ve even heard someone recommend a sparse matrix, but then how do the tag names grow gracefully?

Am I missing a best practice for tags?

All Data Modeling Solutions on Gang of Coders

Total of 20 Data Modeling Solutions