Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (2)

3
votes
3answers
289 views

Is this database fine for calculation?

I am doing an application called as Invoice Application.For that I have my database is like this. Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
2
votes
3answers
306 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
4
votes
2answers
551 views

Designing an aggregation schema

The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it ...
5
votes
2answers
95 views

Per person weighted answers

Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits. My question is I have a set of people for which I want to record needs mapped to ...
2
votes
2answers
263 views

Writing to a DKNF database, but reading from a de-normalized table for performance?

Would the following approach be reasonable? The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database. The Context: A database for ...
0
votes
2answers
81 views

Tiered permissions to generate conditional data access

I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on ...
2
votes
1answer
254 views

Is having all entity tables inherit from one main entity table a bad idea?

My co-worker is designing a schema where most entities (like contacts, quotes, tasks) inherit from an "entity" table, which keeps track of things like which tenant owns the entity, and chagnes to the ...
4
votes
1answer
126 views

unique constraint on a set

With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005? To illustrate the problem, I make an example: A tag_bundle consists ...
0
votes
0answers
57 views

When is should/shouldn't the Entity Attribute Value (EAV) pattern be used? [duplicate]

Possible Duplicate: Is there a name for this database structure? The Entity Attribute Value (EAV) pattern usually has three columns: foreign key, attribute, and value. Attributes of data ...
0
votes
2answers
450 views

Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?

Operating system used is linux. I have tried Navicat and SQL Power Architect. They did display relations between tables in the same schema. I have some foreign key constraints which reference tables ...
4
votes
2answers
3k views

database schema design for school system

I am trying to create a database to store information about a school. I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes ...
5
votes
2answers
1k views

free database modelling software

What is available in the realms of database modelling software that is free. I am developing an SQLExpress database. CA ERWin - community version although is free, I can't see an option to allow ...
2
votes
5answers
247 views

How to shard 14 gb of data for archival database?

We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state. We now want to store this in a database. There would be ~130 million records. The ...
0
votes
0answers
206 views

Schema for Google Docs-like sharing

Am trying to design a nicely normalized schema for sharing entities in a database, with good referential integrity. It should work similarly to Google Docs sharing. Here is what I have so far: ...
1
vote
1answer
476 views

how to avoid duplicated column in two different tables?

OK, I'm inexperienced in terms of database design, so hopefully my question isn't too silly. I have 2 tables users and user_profiles these two tables has one-to-one relation between them. ...
-1
votes
1answer
609 views

How to link data from a table to another table in sqlite database?

I need to link data from a table to another table. I'm not sure how to do this. I have never link a table to another table. Anyone can help me? Or maybe can give me examples or sample codes? Thanks =) ...
3
votes
2answers
664 views

Database Design (Stores + Products)

I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200. My question on DB design, is how do i keep track of which products ...
7
votes
3answers
1k views

Appropriate multi-language schema, or overkill?

UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/ I am building ...
1
vote
3answers
79 views

Decorating an existing database table with information that no one else cares about

We have a table that many processes read from. Some of them want to keep track of the fact they have processed this data, whereas others simply rely on fetching the data from the table based on user ...
3
votes
2answers
60 views

Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?

I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...
1
vote
1answer
439 views

What is the correct way to store a list and its items in this instance?

The app I'm working on revolves around projects. Each project can contain several lists, and each list is made up of several items. Each list relates to a single project. Is it better to have one ...
4
votes
1answer
200 views

How to set constraints on UDT in SQL Server 2005 - 2012

I'm creating my UDT: CREATE TYPE [dbo].[Code] FROM VARCHAR(20) NOT NULL As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that ...
3
votes
1answer
214 views

Database Relationships overview appreciated

I am helping to create a database via salesforce for storing information about Non-profit organizations. It has primarily 4 entities in the schema: Agency, Program, Project, and Contact, where Agency ...
2
votes
2answers
256 views

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures. I have the following tables setup. I normalized the tables to avoid using ...
7
votes
1answer
218 views

How can I make my database more concise?

I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain ...
5
votes
1answer
645 views

Denormalized table designs in normalized database?

There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
1
vote
1answer
468 views

Is PostgreSQL 9.2 autovacuum sufficient for a busy table?

I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database: CREATE TABLE ch02.markers ( ff_id SERIAL PRIMARY KEY, col1 boolean, col2 smallint, created_at timestamp, lat double precision, ...
0
votes
3answers
271 views

Is adding rows better then adding columns in case their value is not consistent?

In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be ...
4
votes
1answer
386 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
3
votes
2answers
8k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
4
votes
3answers
1k views

Primary key should be chosen so as attributes never change?

I was reading about keys in this DatabaseSystems book - The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person ...
1
vote
2answers
798 views

How do I normalize a database with lots of many-to-many relationships?

I am building a database that is based around a business project, in this case called a "Program." Each Program will have multiple Reports, Clients, and Partners. My first thought was to place each ...
2
votes
2answers
380 views

Help with Vehicle Service database

I am using the Desktop Vehicle Maintenance template in access, but i am trying to cater it towards our farms needs. We will be keeping track of the service's done on Trucks, Tractors, Equipment, etc. ...
2
votes
2answers
3k views

Does order matter for composite primary key in Oracle or any database?

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order ...
2
votes
2answers
115 views

Long JOINS Returns No result all tables have one to many relationship with a particular table [closed]

I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is ...
2
votes
4answers
206 views

What free database software support sequences

I have a requirement that a database must support sequences. I know oracle and PostgreSQL support it. Are there any other free database software that support database sequences? it would be great if ...
3
votes
2answers
631 views

Optimizing real-time Database table in Microsoft SQL server 2008

We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state ...
3
votes
3answers
230 views

Database tables design

If I have, in my Java code, the following classes: Patient :contains instance of anther classes (mentioned bellow) PatientBasicInformation: contains PatientId, phoneNumber, Name, Gender, Age. ...
3
votes
1answer
154 views

Database design - many to many

I have one MySQL table which contains: a question 4 possible answers separated by a comma a number (0-3) representing the correct answer. unique id with about 2000 entries. I also have another ...
5
votes
2answers
294 views

Help with good RDBMS transactional schema design that mimicks a sports league

I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are: Well normalized Transactional Application ...
2
votes
1answer
243 views

Best approach to design a database model for this situation? Clients and records

We need to store the following information for each client: Three types of record per file, for example (obviously, with real data): HDR2007022399999900000000000Soft/XX 0.0 XXX ...
1
vote
2answers
526 views

MySQL table design / architecture, table is too big

I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table. I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in ...
41
votes
5answers
4k views

Is there a name for this database structure?

Noob question, sorry if it's too basic for this exchange. We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one ...
4
votes
1answer
427 views

Table structure for keeping track of multiple similar accounts

I'm creating a database to keep track financial information for about 30 different accounts. So far this has all been handled with each account as its own Excel table with the different years across ...
3
votes
1answer
436 views

Simple survey database design [closed]

I'm creating a survey for visitors of my event. However it's been a while since I created a database. So I need some help. I found some solutions but they are way too extensive and that is something ...
3
votes
1answer
1k views

Under what conditions are polymorphic associations used? What are the alternatives?

As I understand it, polymorphic relationships among tables in a database allow one table to have one foreign key that references many tables. I have found that tags are a common application of this ...
3
votes
1answer
218 views

Serialized objects vs multiple tables

What would be the best way to store several entries in a database, each containing a list of other entries? An example of this would be a table of receipts, where each receipt has some information ...
4
votes
2answers
332 views

What is the primary key on a temporal table?

If I use the following table design to capture history...: CREATE TABLE MyTable ( insertion_timestamp TIMESTAMP, deleted_flag BOOLEAN, natural_key INT, attribute VARCHAR ); ... then ...
180
votes
15answers
65k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
2
votes
2answers
243 views

creating a database scheme for storing information about an item, which option is better?

I have a database which stores information about various items. In particular, for each item I need to store the name, type and color. Each type can have a various amount of different colors. For ...