I've seen a few different naming schemes throughout multiple languages when it comes to many-to-many relationships, so I was wondering: what naming scheme do you use for naming these relationships? Please include the platform for which you are developing. Use Posts and Tags as examples for the tables that would be related. Examples: ...
We have an application that has 1000+ databases and 600+ sprocs. Each database represents a different client. Problem: We need to move this to a single database while creating as little effect on the ui as possible, meaning dont change all the sproc signatures at 1 time. The connection string currently sets the database attribute, a ...
Is there an easy and fast way to calculate the rank of a field in a database using Ruby on Rails? For example, if I have a math_scores table, and would like to find a given a MathScore.find(:all, :condtions => ... :order =>...) then iterate through all of them to find out where the test score falls, but there's got to be a more straight...
Are staging tables an anti-pattern that is used when rpc (such as Java RMI or some kind of Web Service call) or messaging queue (such as JMS) would be a better solution, or are there problems better served by staging tables? To clarify: By staging tables I mean those cases where records are appended to a table or tables by a process wh...
I've crossed this problem in, oh, every application I've ever written. I'd like a final consensus answer! Is this the most normalized/efficient/proper way to store contact methods or any other data along this pattern in a database? Contact { PK ContactId, ... } ContactContactMethod {PK FK ContactId, PK FK ContactMethodId, Key, Commen...
I am facing a performance (it can lead to scaling issue later on) issue at the moment. The application I am working on is quite complex and it’s running on SQL Server 2005, I need to join 6 - 7 tables to get the desired data. Each table contains more than 100,000 rows of data so far. The database schema can not be changed (must stay as i...
Accessing Tables by Name I need to reference a table name by a string value given to me. How would I go about doing this in Postgres? Using stored procedures with PLPGSQL is an option, however other languages will be unavailable. For example: SELECT count(*) FROM some_function_that_returns_data_by_table_name('mytable'); I have the f...
Hi all, I am currently in the process of debugging a database performance issue. I have run extended trace on the suffering oracle box to obtain a trace file which I have analyzed using tkprof. One thing that caught my eye straight away was the following output; Elapsed times include waiting on following events: Event waited on ...
Hi, I'm building a movies website... I need to display info about each movie, including genres, actors, and a lot of info (IMDB.com like)... I created a 'movies' table including an ID and some basic information. For the genres I created a 'genres' table including 2 columns: ID and genre. Then I use a 'genres2movies' table with two colum...
I have read several times that after you delete a row in an InnoDB table in MySQL, its space is not reused, so if you make a lot of INSERTs into a table and then periodically DELETE some rows the table will use more and more space on disk, as if the rows were not deleted at all. Recently I've been told though that the space occupied by ...
hi, i have 2 tables in an oracle database, which has the same column-names and types. for example: Table1: id, name, comment Table2: id, name, comment How can i show all data from both table in one view? ...
I would like to be able to search a table as follows for smith as get everything that it within 1 variance. Data: O'Brien Smithe Dolan Smuth Wong Smoth Gunther Smiht I have looked into using Levenshtein distance does anyone know how to implement this with it? ...
The question is how long have these customers been jerks on any given date. I'm working against Sybase For this simplified table structure of table history_data table: history_of_jerkiness processing_date name is_jerk --------------- ----- ------- 20090101 Matt true 20090101 Bob false 20090101 Al...
I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Thanks in advance. Of course the example is just... an example. So please don't worry about the semantics. ...
I'm trying to model timesheets, billable hours, projects, users Whats the best way to represent these in a database Elaborating more on the question: My biggest predicament is how to keep date, hours, project together One possible solution User (PK id, name) Project (PK id, name, FK user_id) BillableHour (PK id, FK project_id, hour...
What are your methods of linking data spread over multiple databases architectures (think MySQL vs PostgreSQL etc), into a single application? Would you create giant hashtables/arrays to match content against one another? Are there other, more effective and less memory-consuming options for doing this? If you were to use data both fro...
I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour? The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below. This is my explain output from...
I write the code for my own website as an educational/fun exercise. Right now part of the website is a blog (like every other site out there :-/) which supports the usual basic blog features, including commenting on posts. But I only have comments enabled for logged-in users; I want to alter the code to allow anonymous comments - that is...
I have a Database class which contanins the follwing methods: public bool ExecuteUDIQuery(string query) // UDI = Update Delete Insert public bool ExecuteSelectQuery(string query) public bool ExecuteSP(string sp, string[,] parms) public int ExecuteSPReturnValue(string sp, string[,] parms) The results of the methods are stored in priva...
Hi I am using following statement in select portion of the query: extract(XMLTYPE(doc.payload),'/SHOW_SHIPMENT_005/DATAAREA/SHOW_SHIPMENT/SHIPMENT/SHIPITEM/DOCUMNTREF/DOCUMENTID') it works well if the doc.payload contains XML without DTD declaration, but raises an error when DTD declaration is present as Oracle tries to validate the d...