database-design

Fluent NHibernate table per subclass inheritance mapping

I'm new to NHibernate and Fluent NHibernate. I'm wondering how to properly use Fluent NHibernate with the "table per subclass" mapping strategy. This is an example of what I'm after. More specifically though, I need a way to break the subclass mappings into separate files. Also, when adding records, I need NHibernate to first insert in...

How many significant digits should I store in my database for a GPS coordinate?

I have in my MySQL database both longitude and latitude coordinates (GPS data). It's currently stored as: column type ------------------------ geolat decimal(10,6) geolng decimal(10,6) Question: Do I really need a data type as large as decimal(10,6) to properly store coordinate data? Since I have a combined index on the...

Database "key/ID" design ideas, Surrogate Key, Primary Key, etc

So I've seen several mentions of a surrogate key lately, and I'm not really sure what it is and how it differs from a primary key. I always assumed that ID was my primary key in a table like this: Users ID, Guid FirstName, Text LastName, Text SSN, Int however, wikipedia defines a surrogate key as "A surrogate key in a...

Which is the most common ID type in SQL Server databases, and which is better?

Is it better to use a Guid (UniqueIdentifier) as your Primary/Surrogate Key column, or a serialized "identity" integer column; and why is it better? ...

Using SQLite or MS Access, how should the tables be organized to model the logic of this 3D structure?

Here is an explanation of how the elements are related. tl;dr: Intensity is strongest at the center of the diagram. The basic emotions (in the arms) combine to form secondary emotions in the outer circle. (i.e., Anticipation + Joy = Optimism) What is the best way to design a database to model this set of relationships? ...

PHP/MYSQL - Backing up mysql data that changes by the second.

I have a mysql table (A) that's taking in hundreds to thousands of inserts per second. Every 15 minutes I'd like to move all that data to table (B) and delete all the data from (A) without interrupting/missing the new rows going in. Suggestions? Tips? ...

What is the maximum size of a primary key in Firebird?

I need to set a Varchar(255) field as the primary key of a database table in Firebird 2.1. I get error messages saying that the field size is too large. I'm using UTF8 as my character set and the default page size of 4096. Is it possible to do this in Firebird? I need to ensure that this column is unique. ...

Composite foreign keys

This comes up a lot for me. In SQL Server 2008, I have 3 tables. 2 with unique keys and 1 which is a mapping between them. Example: People Events Schedule ------ ------ -------- PersonId EventId ScheduleId Name Place PersonId EventId Rsvp ScheduleId isn't needed if I make a com...

Need to upload images to a mdb database

I have a site called www.centuryautosd.com they will be adding in their new cars as they come on the lot. I've got the admin area done but I'm stuck on how to enable them to upload their pictures of the cars. If you can help me I would greatly appreciate it. I told them the site would be done today... If you need the password for the...

Deleting a field in database by setting an is_deleted field and using index

Hi, I'm writing an app which I want to delete records by setting an is_deleted field in the database to true and is defaulted to false. This works fine but I'm running into an issue when I index unique another field I can't insert a field with the same entry although the old one is practically deleted but not from DB point of view. Do...

users and permissions

I want to give some users certain permissions. Would this be the best way? I don't want to make groups like: admin, moderator, normal and so on. I want to be able to give one user maybe access to make news post and another one create poll, and maybe another delete polls. I'm thinking tables like this: CREATE TABLE `users` ( `id` medium...

table design + SQL question

I have a table foodbar, created with the following DDL. (I am using mySQL 5.1.x) CREATE TABLE foodbar ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, weight double not null, created_at date not null ); I have four questions: How may I write a query that returns a result set that gives me th...

Should I put my entire site on one database or split into two databases?

My webapp that I am developing will incorporate a forum (phpbb3 or smf), a wiki (doku wiki flat file storage), and my main web app. All of these will share a User table. The forum I expect to see moderate usage (roughly 200 posts a day) to begin with and will increase from there. The main web app will be used heavily with triggers, mysq...

How bad is it to change the structure of big tables?

My target DB is MySQL, but lets try to look at this problem more "generic". I am about to create a framework that will modify table structures as needed. Tables may contain a hundred thousands of records some day. I might add a column, rename a column, or even change the type of a column (lets assume that's nothing impossible, i.e. I mig...

To normalize or not to normalize user_ids

In my Rails application, I have a variety of database tables that contain user data. Some of these tables have a lot of rows (as many as 500,000 rows per user in some cases) and are queried frequently. Whenever I query any table for anything, the user_id of the current user is somewhere in the query - either directly, if the table has ...

The best choice for Person table primary key

What is your choice for primary key in tables that represent a person (like Client, User, Customer, Employee etc.)? My first choice would be an SSN number. However, using SSN has been discouraged because of privacy concerns and different regulations. SSN can change during person lifetime, so that is another reason against it. I guess th...

Database slow retriving/updating/inserting problem with more than 5mil records in each table

How to structure database to avoid slowdowns? (Engine: MyISAM) Currently i have database with more than 5milion records in one table that causes slow data retrieving. I'm currently searching for ways to structure database to avoid this kinds of database. (Database Engine MyISAM) Tables that cause problems are posts and comments having ...

What's better? Having 100 Tables with each 1,000 rows, or 10 Tables with each 10,000 rows?

With 10 Tables, I would have no joins. With 100 Tables, I would have one join per query. Which would show better performance? ...

Adding fields to optimize MySQL queries

I have a MySQL table with 3 fields: Location Variable Value I frequently use the following query: SELECT * FROM Table WHERE Location = '$Location' AND Variable = '$Variable' ORDER BY Location, Variable I have over a million rows in my table and queries are somewhat slow. Would it increase query speed if I added a...

Is it a good practice to put the tables of different versions of a website(no data sharing among these versions) in one database?

I am developing a website. There is an English version, Japanese version and Chinese version. Different version is for different language speakers. If you are a registered user of the English version, and you want to use the Japanese version, you still need to register on the Japanese version. So should I create one database and put all ...