Will extending the size of a varchar field in MySQL affect the data thats inside it?
I am going to be extending the column from VARCHAR(350) to VARCHAR(800) - will affect any of the strings that are inside the column already? ...
database-design
How to normalize a database where different user groups have different kinds of profiles?
My application database has a Groups table that separates users into logical roles and defines access levels (admin, owner, salesperson, customer service, etc.) Groups has many Users. The Users table contains login details such as username and password. Now I wish to add user profiles to my database. The trouble I'm having (probably ...
MongoDB Schema Design - Many small documents or fewer large documents?
Background I'm prototyping a conversion from our RDBMS database to MongoDB. While denormalizing, it seems as if I have two choices, one which leads to many (millions) of smaller documents or one which leads to fewer (hundreds of thousands) large documents. If I could distill it down to a simple analog, it would be the difference between...
Need advice to change my database design.
I need to change the way I am storing information in the DB. Because the query works slow with the old model I had developed. General problem is following. 1) I have list of courses, and each course has list of tags describing general content of the course. For instance, the course called "Database Management Systems" could have follow...
Tables as relations in ER diagrams
Assume I have the following tables (**bold** - primary key, *italics* - foreign key): patient(**patient_id**, name) disease(**disease_id**, name) patient_disease(**p_d_id**, *patient_id*, *disease,_id* ) I want to draw the ER diagram for this. My idea is to make two entities, one for patient and one for disease, then make a n-to-n rel...
How do we greatly optimize our MySQL database (or replace it) when using joins?
Hi there, This is the first time I'm approaching an extremely high-volume situation. This is an ad server based on MySQL. However, the query that is used incorporates a lot of JOINs and is generally just slow. (This is Rails ActiveRecord, btw) sel = Ads.find(:all, :select => '*', :joins => "JOIN campaigns ON ads.campaign_id = campai...
Database layout for an application with geocoding features using geokit
I'm developing a real estate web catalogue and want to geocode every ad using geokit gem. My question is what would be the best database layout from the performance point if i want to make search by country, city of the selected country, administrative area or nearest metro station of the selected city. Available countries, cities, admin...
Why is it bad to use boolean flags in databases? And what should be used instead?
I've been reading through some of guides on database optimization and best practices and a lot of them suggest not using boolean flags at all in the DB schema (ex http://forge.mysql.com/wiki/Top10SQLPerformanceTips). However, they never provide any reason as to why this is bad. Is it a peformance issue? is it hard to index or query prope...
Database Design for a double entry accounting system
Should journal entries be recorded in a database design? In the real world it makes sense to keep a daily entry book, then later transfer the daily entry book into double entry accounts. but in the computerized version, doing this produces duplicate records and that doesn't quite make sense? Real life: User ---> journal day book (singl...
How best can I extract a logical model from a physical DB model
We have made substantial changes to our physical DB, now as it is the end of the project I would like to abstract a logical model from this, to allow me to generate schemas for both Oracle and SQL Server. Can anyone guide me as to the best way to achieve this. I was hoping TOAD data modeller would help but I can't seem to see any optio...
Database design efficiency with 1 to many relationships limited 1 to 3
This is in mysql, but its a database design issue. If you have a one to many relationship, like a bank customer to bank-accounts, typically you would have the table that records the bank-account information have a foreign key that keeps track of the relationship between account and customer. Now this follows the 3rd normal form thing and...
Is this the right way to organize my database tables?
So I'm making a website that allows users to build contact lists. So their are users, the users have lists, and the lists have contacts. It seems to me that I need 3 tables for this but I just want to make sure. There would be a User table of course, and then a "List of Lists" table that has the username, and listname, as primary key a...
What are the reasons *not* to use a GUID for a primary key?
Whenever I design a database I automatically start with an auto-generating GUID primary key for each of my tables (excepting look-up tables) I know I'll never lose sleep over duplicate keys, merging tables, etc. To me it just makes sense philosophically that any given record should be unique across all domains, and that that uniqueness...
Database schema question
I am designing a data model for a local city page, more like requirements for it. So 4 tables: Country, State, City, neighbourhood. Real life relationships is: Country owns multiple State which owns multiple cities which ows multiple neighbourhoods. In the data model: Do we link these with FK the same way or link each with each? Like ...
In a One to One relationship should i drop one of the table's id column?
I have the following 2 tables in MySQL: Customer(Id, Firstname, Lastname...) Bonus(Id, CustomerId, Value, ...) The relation is One-To-One, every customer has only one bonus.(the CustomerId is unique in the Bonus Table) Q: Should I drop the Id column of the Bonus table? (I want to know why or why not) ...
how to implement full text search in database
I understand that full text indexing and search for a database can be enabled by a lot of pre-packaged products. However, just out of academical curiosity, I wonder how are those full text indexes actually implemented. I have tried to google for results with little answer. Please any feedback would be much appreciated. ...
Is it possible to write a database view that encompasses one-to-many relationships?
So I'm not necessarily saying this is even a good idea if it were possible, since the schema of the view would be extremely volatile, but is there any way to represent a has-many relationship in a single view? For example, let's say I have a customer that can have any number of addresses in the database. Is there any way to list out ea...
Generic Database table design
Just trying to figure out the best way to design my table for the following scenario: I have several areas in my system (documents, projects, groups and clients) and each of these can have comments logged against them. My question is should I have one table like this: CommentID DocumentID ProjectID GroupID ClientID etc Where only on...
Database tables with dynamic information
I've googled this and found that it's almost impossible to create a database with dynamic collumns. I'll explain my problem first. I am making a webshop for a customer. It has multiple computer products for sale. CPU's HDD's RAM ect. All these products have different properties, a CPU has an FSB, RAM has a CAS latency. But this is very ...
Does SQLite multi column primary key need an additional index?
If I create a table like so: CREATE TABLE something (column1, column2, PRIMARY KEY (column1, column2)); Neither column1 nor column2 are unique by themselves. However, I will do most of my queries on column1. Does the multi column primary key create an index for both columns separately? I would think that if you specify a multi colum...