database-design

SQL Server 2008 data types

What are the different SQL Server 2008 data types? I am specifically trying to find the equivalent of a long int. ...

Database Design for Document Library

We are attempting to built the Document Library (File Management) as apart of our core apps. It starts with simple one obviously. I need a feed back regarding the database design for this particular module. Initial Design: File table: FileID FileGUID TenantID FileName FileDescription FileImage FileSize FileExtension ContentType Crea...

Performace enhancements for tables

With MySQL I often overlook some options like 'signed/unsigned' ints and 'allow null' but I'm wondering if these details could slow a web application down. Are there any notable performance differences in these situations? using a low/high range of Integer primary key 5000 rows with ids from 1 to 5000 5000 rows with ids from 20001 to...

lots of columns in a table

A table in our schema is already having 100 columns.We need to add some 600 more columns if we follow horizontal data storage method. If we go for vertical data storage which means creating new table and creating referential integrity with table having 100 columns, there will be issue with joining the table as the table having 100 column...

OO data model - can I ignore Object Relational Mapper constraints?

I am just starting to model the data for a new C# project, which must be persistable. Looks like the most natural OO model will have lots of nested .Net generics. Lists of objects, and these objects will also contain Lists of other generic types, and so on, nested at least three levels deep. Ideally, I'd like to just design the data m...

Database Design: Alternate to composite keys?

I am building a database system and having trouble with the design of one of my tables. In this system there is a users table, an object table, an item table and cost table. A unique record in the cost table is determine by the user, object, item and year. However, there can be multiple records that have the same year if the item is di...

Database design software for a student?

I am taking a database class and have to draw a bunch of different kinds of diagrams. Specifically, I need to draw ERD and dependency diagrams. The instructor does not want any hand drawn diagrams but does not offer any clue as to what software to use. Does anybody have any suggestions? ...

Asking for opinions : One sequence for all tables

Here's another one I've been thinking about lately. We have concluded in earlier discussions : 'natural primary keys are bad, artificial primary keys are good.' Working with Hibernate earlier I have seen that Hibernate default creates one sequence for all tables. At first I was puzzled by this, why would you do this. But later I saw the...

How to design a database where employee can belong to either department or division

Hi there, I have a database where employees, departments and divisions are stored. Each employee can belong to either a division or a department (1-to-1 relationship). To store that data I have created three tables: Employees, Departments, Divisions. How do I correctly wire them to store such relationship. The easiest way is to have in...

Storing OpenID information in database.

This is a database question. I have an openid library already installed and working. I want to use OpenID on a website I'm working on. There are three types of users. Named accounts using email/password to authenticate Named accounts using OpenID to authenticate Anonymous account using OpenID to authenticate All of these users are s...

MYSQL table structure

I am trying to decide whether to create a global view table or 1 for each section. for example, Lets say I have products, categories and pages. Do I have 3 tables e.g: CREATE TABLE `tbl_products` ( `p_id` INT NOT NULL , `p_views` INT NOT NULL , INDEX ( `p_id` ) ) ENGINE = MYISAM CREATE TABLE `tbl_categories` ( `c_id` INT NOT NULL , ...

SQL Server 2008: N small databases VS 1 database with N schemas

I have a database server with few main databases, and few dozens of small ones. These small databases are kind of intermediary/staging databases for data import from various sources into main database. Data import is a daily task. They are all quite similar in structure as the implementation of these data imports are similar, so basical...

Is there a general term for a pairing of tables where one has header information and the other has detail lines?

Is there a general term for a pairing of tables where one has header information and the other has detail lines? For example, a pair of tables describing sales orders, or a pair storing bill of materials data. ...

Three potential db designs

I've been considering three similar database designs, but haven't come up with a very strong for or against regarding any of them. One huge table for content content: int id, enum type, int parent_id, varchar title, text body, text data This would assign each row a type (news, blog, etc), and have fields for common/standard/searchab...

Can you provide some advice on setting up my database?

I'm working on a MUD (Multi User Dungeon) in Python and am just now getting around to the point where I need to add some rooms, enemies, items, etc. I could hardcode all this in, but it seems like this is more of a job for a database. However, I've never really done any work with databases before so I was wondering if you have any advic...

Database "pointers" to rows?

Is there a way to have "pointers to rows" in a database? for example I have X product rows, all these rows represent distinct products but many have the same field values except their "id" and "color_id" are different. I thought of just duplicating the rows but this could be error prone, plus making a small change would have to be done...

How to deal with .1 & .10 with MySQL & Rails

I currently have a MySQL database with a table that has a field that is typed as a decimal. Currently there are values that range from *.1 through *.9 but we now need to be able to handle the following: I need to be able to add *.10 to this field and sort it accordingly. In this use-case .10 DOES NOT equal .1! I need to be able to us...

trying to figure out the best database schema

i want to come up with a generic (if possible) schema to use for a number of different events that i am managing. These events can be weddings, birthday parties, etc So for i have 3 main tables Contact Table - with the usual info like address, phone, etc Events Table - list of events with some info like date, location, etc Now i am t...

Are there useful static analysis tools for databases?

Is there a tool for examining the configuration and schema of a database for dubious fields, relationships and configuration, similar to how static analysis tools like lint will flag dubious lines of code? I'm not necessarily asking for normalization, but surely there's stupid stuff that can be detected without solving Hard AI or the Ha...

Should I use multiple fields or JSON?

Hello. I'm designing a DB and would like to know the best way to store this: I have an user table and let's say it can have 100 item slots, and I store an ID. Should I use JSON ({slot1:123232,slot20:123123123,slot82:23123}) or create 100+ fields (slot1, slot2, slotn)? Thank you. ...