Very often, I want to run a query on one of my users where I want a row stored and associated with that user, in a 1-to-1 relationship. So let's say (this is just an arbitrary example), that I have a table that keeps track of a user's car, along with some info about the car. Each user can have either 0 or 1 cars. If the user has no ca...
I am looking to use this concept in one of my upcoming project. more info: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Please share your experiences good or bad with examples. I am adding more information to make it more broad: I have child items that can have more than one parent (example: a user can belong t...
I want to maintain last ten years of stock market data in a single table. Certain analysis need only data of the last one month data. When I do this short term analysis it takes a long time to complete the operation. To overcome this I created another table to hold current year data alone. When I perform the analysis from this table it...
When SHOW WARNINGS after a EXPLAIN EXTENDED shows a Note 1276 Field or reference 'test.foo.bar' of SELECT #2 was resolved in SELECT #1 what exactly does that mean and what impact does it have? In my case it prevents mysql from using what seems to be a perfectly good index. But it's not about fixing that specific query (as it is an irr...
Hello, I have an application where I'll have repeating events. So an event can repeat by day, "every n days", by week, "every n weeks on Mon/Tue/Wed/etc", and by month, "every n months on the 1st,2nd,3rd,etc". What is the best way to handle this from a table design perspective? I can think of two ways but I'm not sure which one is bett...
For example, in SQLServer, if I have an index with 'unique' set on it, how is that different from a key? How do I know when I want to use a Key vs. an Indexed field? ...
What is the best way to store lots of video in a web application? Hardware, Database, etc. If you were building a YouTube clone, how would you do it? ...
I am using MySql 5.1 Database. I have created a Project database. (Temnplate Database) and want to Create a copy of the same database from the Application, every time, User Creates A new Project. How can I copy and Create a New Database of same structure.?? What is the Command to do so??? Thanks a Lot for Help. ...
Are there any best practices to column ordering when designing a database? Will order effect performance, space, or the ORM layer? I am aware of http://stackoverflow.com/questions/34818/sql-server-does-column-order-matter. I am looking for more general advice. ...
Consider the following scenario: Tables: Employee (EmpId(PK), Name) TeamMembers(TeamId(PK), EmpId(PK)) Project(ProjId(PK), TeamId) I really want to avoid using composite PK, but the only way I see out of the problem is creating a Team table with only 1 column TeamId(PK) (i do not want to store any info associated with the team othe...
I am looking at quite a monstrosity of an application that uses several tables to represent a reporting hierarchy, but eacvh of these tables is identical. The table at the bottom of the hierarchy has the most records, each of which has a ParentID to the rollup table above it, eventually all adding up to only one total in the top rollup ...
Hi, I'm creating a movies website, IMDB.com like.. I'm really new to PHP and programming at all but I have some books and StackOverflow of course :) I have already done lot of the work, but now I have more than 600 lines of code (PHO only) per page and more than 20 database tables only for storing and geting the movie data (many-to-many...
I'm working on a web based medical billing code search engine for my software start-up that will let users search for ICD-9 (and related ICD-10, clinical codes) medical codes used in medical diagnoses and medical billing. The problem with building the data files for the search is that the Center for Disease Control only releases the fil...
I'm developing an iPhone application and am new to Objective-C as well as SQLite. That being said, I have been struggling w/ designing a practical data management solution that is worthy of existing. Any help would be greatly appreciated. Here's the deal: The majority of the data my application interacts with is stored in five tables ...
If I were designing a oil refinery, I wouldn't expect that materials from different vendors would not comply with published standards in subtle yet important ways. Pipework, valves and other components from one supplier would come with flanges and wall thicknesses to ANSI standards, as would the same parts from any other supplier. Inte...
How would I store a pdf in my Rails app so that users can access/download them? In my migration, would i i use the datatype t.binary? Then in my controller/view, how should I present this data? ...
Update 2009.04.24 The main point of my question is not developer confusion and what to do about it. The point is to understand when delimited values are the right solution. I've seen delimited data used in commercial product databases (Ektron lol). SQL Server even has an XML datatype, so that could be used for the same purpose as del...
Say you have an Events model that contains information about the event. But what about things like slugs, titles and meta description that would go into the html? It would seem like such things properly belong as attributes to a Page model rather than to an Events model. Is there a correct way to do this? What are the pros and cons of...
Say you have a Many-Many table between Artists and Fans. When it comes to designing the table, do you design the table like such: ArtistFans ArtistFanID (PK) ArtistID (FK) UserID (FK) (ArtistID and UserID will then be contrained with a Unique Constraint to prevent duplicate data) Or do you build use a compound PK for...
I need to make a chatroom for my Rails app. Are there any good tutorials for writing one? Will it be a non-Rails technology that I simply plug in or are there some Rails libraries that are meant for it? I want to save each message to my PostgresQL database, and I want all previous messages to be visible - a perpetual chatroom. ...