I have a MySQL database. I store homes in the database and perform literally just 1 query against the database, but I need this query to be performed super fast, and that's to return all homes within a square box geo latitude & longitude. SELECT * FROM homes WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? How is the b...
Let's suppose there is a table called AIRPORT and I have to choose between two naming conventions: to name attributes like AP_CODE, AP_NAME and so on or to name them just like CODE, NAME The question is whether it is more efficient to follow the first way or to use synonym (i.e. AP) and reference attributes like AP.CODE? Thanks in adva...
I am trying to push an updated version of my sqlite db to my device and so far have not figured out a way to do it other than renaming it each time (painful). The stale (cached?) copy remains permanently on the device. I understand how to get an updated version into the simulator by clearing the /applications folder, but this does not ...
How can I prevent inner SELECT from returning NULL (when matches no rows) and force query to fail. INSERT INTO tt (t1_id, t2_id) VALUES ( (SELECT id FROM t1 WHERE ...), (SELECT id FROM t2 WHERE ...) ); Side question: is there better way form this query (t1_id, t2_id are foreign keys, but might be NULL) ? ...
Hello, I am trying to find out what are the popular naming conventions for DB tables. Therefor I have 2 questions: Lets say you have a table with persons in it. Each row is a person. How would you call the table - 'PERSONS' or 'PERSON'? Now lets say there is another table named 'PERMISSIONS' and you are creating a new table which is m...
The Facebook API has a call $facebook->api_client->friends_getAppUsers(); and that returns the friend ids of a user who is using the app. Now say I want to select from my db table all the rows that match all those friend ids. The only way I can think of doing it would be SELECT * FROM my_table WHERE uid IN(friend1, friend2, friend3, f...
For an online marketplace product under construction, I have a situation which requires implementing a database sharding solution. I am new to sharding and after reading the posts in this forum I feel a directory based sharding strategy using business entities will be suitable. But I am still not clear about the denormalization and data ...
Suppose I have a Comment class having properties and their methods like public Comment GetComment(Guid id) And public static List<Comment> GetComments() public static List<Comment> GetCommentsByAuthor(Author id) Now, What I usually do is write the database logic for each of the above methods . That said, Now I am seeing BlogEngine...
Hi everyone, Recently I'm exploring NoSQL Databases. I need an advice about how to store data in the most optimal and efficient way for a given problem. I'm targeting MongoDB, now. However it should be the same with CouchDB. Let's say we have these 3 Models: Story: id title User: id name Vote: id story_id user_id I want ...
Hello, I have a program written in c# visual studio 2008 with SQL server 2005 (.mdf) database. Here is part of the code: ... SqlCommandBuilder cb; cb = new SqlCommandBuilder(dataAdapter); String[] dataList =new String [8]; ... DataTable resultTable = new DataTable(); ...
Hi, What is the best approach to handle Data Concurrency issues in a 3 layered Application (WCF + LinqToSql or EF) with detached entities or DTOs ? Thanks. ...
I use JMeter to test my web application. and my test case is to add a record in database. and then I set the "number of thread user" to 20, it means that it will simulate 20 users work at the same time. and then I run the test case. But finally i found the system didn't create 20 records in database, but instead just create 13 records in...
One of my clients has mentioned the term 'Database House Keeping' in his tender specs. I am not sure about the term, please help. ...
I have 3 tables called: Applications (id, name) Resources (id, name) ApplicationsResources (id, app_id, resource_id) I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource. So the question is, what is the best way to do this ...
Can anyone please give me any example of situation in a database-driven application where I should use Flyweight pattern? How can I know that, I should use flyweight pattern at a point in my application? I have learned flyweight pattern. But not able to understand an appropriate place in my database-driven business applications to use ...
I am creating a simple todo app where I have 2 types of tasks. 1) regular tasks - These have a due date 2) recurring tasks -These are poped up as reminders on specified date. They can be created either as weekly or monthly reminders. If created for a week, it will be poped up on each week (on a specified date on the week). Likewise for...
No one knows. And even if you work at Twitter, you probably won't let us know the secret. So, let us all post our theories on how this communication tool database is designed! It definitely can't follow the standard replication architecture, right? It's got to do some sharding, partitioning, etc, etc. Let me know what you think! ...
Is there any testing framework for Data access tier? I'm using mysql DB. ...
How to create/drop databases which have integers as their names, from the command line? Using GUI clients, I can create/drop them fine. However, 'create/drop database' does not seem to work because the names are integers. Using GUI clients is fine with me, but curious to know: are there any workarounds to do this in command line? Not...
I have a script file for MySQL that needs to be run on about 30 databases. Here's a snippet: ALTER TABLE <whatever_database>.`tblusers` ADD COLUMN `availability` ... ALTER TABLE <whatever_database>.`tblusers` ADD COLUMN `reliability` INTEGER ... There are many more lines in this script and I'd like to automate it in a loop of current...