Auto-incrementing primary keys are useful for several reasons: They allow duplicate user names as on Stack Overflow They allow the user name (or email address, if that’s used to login) to be changed (easily) Selects, joins and inserts are faster than varchar primary keys as its much faster to maintain a numeric index As you … Read more
A Primary Key is really just a candidate key that does not allow for NULL. As such, in SQL terms – it’s no different than any other unique key. However, for our non-theoretical RDBMS’s, you should have a Primary Key – I’ve never heard it argued otherwise. If that Primary Key is a surrogate key, … Read more
You have this python code: c.execute(“insert into medicoes values(?,?,?,?,?,?,?)” % keys) which I think should be c.execute(“insert into medicoes values (?,?,?,?,?,?,?)”, keys) since the % operator expects the string to its left to contain formatting codes. Now all you need to make this work is for keys to be a tuple (or list) containing the … Read more
WGS84 datum are usually given as coordinates in a fully decimal notation, usually with 5 decimal places, so for latitude (-90 to +90) you could use decimal(7, 5) (-90.00000 to 90.00000), for longitude you could use decimal(8, 5) (-180.00000 to 180.00000). .00001 gives an precision of around a meter at the equator The DECIMAL/NUMERIC data … Read more
In the software industry you have some standards that are really standards, i.e., products that don’t comply with them just don’t work. File specifications fall into that category. But then you also have “standards” that are more like guidelines: they may defined as standards with point-by-point definitions, but routinely implemented only partially or with significant … Read more
No, it’s not OK. Circular references between tables are messy. See this (decade old) article: SQL By Design: The Circular Reference Some DBMS can handle these, and with special care, but MySQL will have issues. Option 1 As your design, to make one of the two FKs nullable. This allows you to solve the chicken-and-egg … Read more
I like the Modified Preorder Tree Traversal Algorithm. This technique makes it very easy to query the tree. But here is a list of links about the topic which I copied from the Zend Framework (PHP) contributors webpage (posted there by Posted by Laurent Melmoux at Jun 05, 2007 15:52). Many of the links are … Read more
Well why don’t you just ask? 🙂 Let me try to pin down my understanding of your requirement. It seems to me that you are looking at a thread being a linear list (not a tree) of messages between two people. I would think that you might want to allow more people in than just … Read more
It’s a matter of choice actually and this question can raise opinion based answers from my point of view. What I always do, even if it’s redundant is I create primary key on auto increment column (I call it technical key) to keep it consistent within the database, allow for “primary key” to change in … Read more
NoSQL databases approach modelling from a different point of view generally. NoSQL modelling usually begins with asking questions on how the data in the database is going to be queried, so the main modelling is ‘what are the list of questions that I want to ask of my database’, whereas with SQL databases, modelling usually … Read more