Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the "profiler" tool for SQLServer, but I'm not aware of a similar tool included with Postgres. ...
I am working on a rewrite of a very old Perl project. This project for employee performance evaluation. Every year an employee will be evaluated by his supervisor(s). The project was first put into use in 1994 using mysql. In 1995, a new mysql database was created for 1995 evaluation and the source code was modified. Then 1996, 1997, ....
I've embarked on a project that is proving considerably more complicated than I'd first imagined. I'm trying to plan a system that is based around boolean (true/false) questions and answers. Users on the system can answer any questions from a large set of boolean (true/false) questions and be presented with a list showing the most simila...
I have a Graph object (this is in Perl) for which I compute its transitive closure (i.e. for solving the all-pairs shortest paths problem). From this object, I am interested in computing: Shortest path from any vertices u -> v. Distance matrix for all vertices. General reachability questions. General graph features (density, etc). T...
My application wants to store a list of international phone number in a mysql database. Then the application would need to query the database and search for a specific number. Sounds simple but it's actually a huge problem. Because users can search for that number in a different format we'll have to do a full scan to the database each ...
I want to create Table-Per-Type database table inheritance. Simply base table RowElement will have 2 children tables. First child table Lyrics will inherit all parents RowElement's columns and will just add one more column. Second child table ChordUse will only many-to-one relationship to table Chord. This is how my database schema lo...
Hi, I'm doing a forum script in php, i'm trying to think the best way to flag topics each user has read or not. Once the user logins into forum i can mark what's been read or not depending on his last login time, that's fine, now what's the best approach to mark each topic each user clicks? I was thinking in adding cookies but that wo...
I'm maintaining a reservation's system for a holiday park. I was wondering if it is common practice to keep a Available Accommodations table which could look like this: Acc AvailableFrom AvailableTo 1 7/1/2010 7/31/2010 2 7/8/2010 7/15/2010 3 7/15/2010 7/20/2010 Because I think it is easier ...
Hi there! I'm searching in a way to limit the queryset which I can get through a model. Suppose I have the following models (with dependencies): Company |- Section | |- Employee | |- Task | `- more models... |- Customer | |- Contract | |- Accounts | `- other great models ... `- some more models... It should be n...
We're building an application that has a database (yeah, pretty exciting huh :). The database is mainly transactional (to support the app) and also does a bit of "reporting" as part of the app - but nothing too strenuous. Above and beyond that we have some reporting requirements - but they're pretty vague and high-level at the moment. ...
Env: mysql How should I go about modeling the event duration for the following scenarios... Today is Monday (start of the week) One day event scheduled between 10 AM - 5 PM today. Every day event open from 10 AM - 11 AM from today till 1 week. (inclusive of weekends) Every day event open from 10 AM - 11 AM from today till 2 weeks. (e...
Hello Hopefully someone may be able to quickly advise the best way for me to model my ERD (I'm using IDEF1X & ERWin not Chen Notation) although it doesn't really matter. Basically I have Three Entities Organisation Person Trustee Business rules state that a Trustee can be either a person or organisation. My question is, would i...
What is the best way to store occurrences of an event in a database so you can quickly pull reports on it? ie (total number of occurrences, number of occurrences between date range). right now I have two database tables, one which holds all individual timestamps of the event - so I can query on a date range, and one which holds a tota...
I have a MySql db with innoDB tables. Very simplified this is how two tables are layed out: Table A: controlID(PK) controlText Table B: controlOptionID(pk) controlID(FK to table A) controlOptionType controlOptionValue So many controlOptions(table B) can reference one control(giving that control m...
Hi, I am trying to run the below query to create a table in mysql and I am getting an error. create table newtable ( version_id int(11) auto_increment not null, test_id int(11) default version_id, primary key(version_id) ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corr...
I have a structure of about 10 tables. This structure works great for data entry. However, I also need to preform complex and fast searches on that dataset. There are three approaches to this I can think of: Join all those tables in select. This is pretty slow and likely not a good approach. If it's relevant, database is Informix; I've...
A database exists with two tables Data_t : DataID Primary Key that is Identity 1,1. Also has another field 'LEFT' TINYINT Data_Link_t : DataID PK and FK where DataID MUST exist in Data_t. Also has another field 'RIGHT' SMALLINT Coming from a microsoft access environment into C# and sql server I'm looking for a good method of import...
Hi, I have the below table. create table mytable( physical_id int auto_increment, logical_id int, data varchar(20), version_start_date datetime, version_end_date datetime, primary key(physical_id), unique key(logical_id,version_start_date, version_end_date) ); The idea behind the schema is, I want to keep track of modification to ev...
Suppose I have the following tables in my database: Now all my queries depend on Company table. Is it a bad practice to give every other table a (redundant) relationships to the Company table to simplify my sql queries? Edit 1: Background is a usage problem with a framework. See http://stackoverflow.com/questions/3331310/django-limit...
I have two pieces of data: 1) User and 2) Portfolio. Each user has only one portfolio. At the registration process user has to fill out the following fields: First name Last name Porfolio title Portfolio url Email Password My DB structure at the moment looks like this: CREATE TABLE `User` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...