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)

2
votes
2answers
815 views

Model a family tree database in Access 2007

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
2
votes
2answers
474 views

mysql products db design

I am building a products site with php and mysql(no shopping cart). I have 400k products, the main site with 2 methods for query. query for products types, all the query by menu, cloud tags (no ...
3
votes
3answers
661 views

How do I design a database for continents, countries, regions, cities and POIs?

I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, ...
2
votes
1answer
138 views

New table or new column

Apologies in advance if this is a dumb question. Background: I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves ...
6
votes
1answer
2k views

2NF decomposition + database normalization

If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
2
votes
1answer
3k views

Concept to keep track of changes in all tables

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions). At the moment there are two ...
2
votes
1answer
854 views

How to create a train schedule in SQLite3?

I am interested in what a SQLite3 database schema would look like for a train timetable. Generally this is presented in table format as the final result. Sample Schedule for M to F STATION A ...
4
votes
1answer
145 views

Beginner design of biological sampling database

As a complete newcomer, I would like to practise designing a database based on the biological data my lab has been accumulating over the years. Here is an explanation (I am simplifying/abstracting ...
5
votes
1answer
821 views

Survey database design: first version. Are there errors?

I have to represent surveys with questions like these ones: Studying those groups of questions I found these rules: A survey has questions' groups. A questions' group has a name and questions ...
1
vote
1answer
187 views

Choosing the right field data type in SQL Server 2008

I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters ...
4
votes
2answers
1k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is ...
7
votes
5answers
3k views

Survey database design: associate an answer to a user

I'm doing the conceptual model for a survey database. The goal is store the answers given by users (it going to be an Android app). I have three entities: user, question and option. A question will ...
3
votes
3answers
282 views

How to prevent redundant relationships in data?

If I want to link users with other users. Would this be a good way to do it: I'm asking because I probably get double data like this if i don't do specific checks: +------+-------------+ ...
4
votes
1answer
747 views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
2
votes
2answers
1k views

Do I need a separate Id column for this “mapping” table?

I have a table of Producers and a table of Products, both of which are of the form: Id - int, Primary key Name - nvarchar A Producer can carry multiple Products, so I was going to create a table ...
5
votes
1answer
324 views

Dimension Help - Deciding Fact or Dimension

We have the following Dim and Facts: Customer Dim: SCD Type 2, Info about the customer ie first purchase date, name, address, etc Product Dim: SCD Type 2, about our products Customer Snapshot ...
1
vote
0answers
85 views

User defined content specific permissions with varying conditions. How to design tables? [closed]

I'm working on a privacy settings problem, which is proving to be cumbersome to design. Essentially I have: User created content User can define access groups for content. Access groups vary ...
3
votes
1answer
1k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
2
votes
1answer
138 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
1
vote
1answer
83 views

Have less tables with a lot of data or a number of tables with smaller amounts of data?

Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here). There are 2 different ...
4
votes
1answer
156 views

Spatial data modelling tools

Is there anything like MySQL workbench for PostGIS? Something that supports SQL output and geometry types would be nice. CLARIFICATION: I am trying to implement the Land Administration Domain ...
1
vote
0answers
379 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
4
votes
2answers
362 views

Maintaining referential integrity in a booking system

I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005. I have 3 tables (simplified for this question). Table 1- CourseSize table- this determines the ...
1
vote
1answer
55 views

Handling newsletter mail subscribers data

A simple data structure for subscribers is email, name, phone, building. Currently, Building is a VARCHAR field, however data contained in the field is actually mostly numeric. How can I change the ...
1
vote
2answers
406 views

Where can I find a good tutorial or book for various MySQL performance guidelines?

Can anyone refer me a good book or a small tutorial for various use cases for database design on MySQL for high load test/high availability scenarios. It should include the following: What are ...
2
votes
3answers
311 views

How to maintain referential integrity of an object graph when a node is deleted

In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained? For example: A product is deleted, how is the invoice containing ...
1
vote
2answers
2k views

Adding Row Version to SQL Server table

I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
1
vote
1answer
2k views

What is different bettween Cassandra NoSQL data model and SQL data model

can any one tell. cassandra data model and sql data model how we can define row and column with cassandra data model?
0
votes
1answer
297 views

SQL DB design to support user feeds (in application like facebook)

I have a social network server with a MySql DB. I want to show the users feeds like done in Facebook. Example - UserX now Friend with userY, userX did like on postX etc. Currently I have table: C1 : ...
5
votes
1answer
345 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...
5
votes
2answers
218 views

How should an object that can reference an object of multiple types be modeled in a relational schema?

Apologies for the obscure question, it may make more sense with a concrete example: In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
0
votes
1answer
274 views

Data base structure of a subscriber list

I am building a application that allow different user to store the subscriber information To store the subscriber information , the user first create a list For each list, there is a ListID. ...
1
vote
3answers
88 views

Need suggestion for placing foreign keys in the databases

I have the database for customers. The customers table is something like this +------------------+ | Customers | +------------------+ | id (PK) | | business_email | | business_name ...
6
votes
2answers
2k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
3
votes
2answers
1k views

What type of datastore should I use for high volume time series data?

Imagine a project which requires to be able to process 50,000+ (tiny ints) writes per second. The data, once inserted, won't change. The data is a measurement on a particular time. Sub second ...
14
votes
4answers
3k views

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
2
votes
1answer
226 views

Using Foreign Keys in database

I am doing a small application for Invoice.In that the tables will be like this === invoice === id (pk) customer_id (fk) invoice_title invoice_issue_date due_date description created_by updated_by ...
0
votes
2answers
635 views

how to design movies with show times database structure

how to design movies with show times database structure I'm building simple local cinemas guide website using Yii PHP framework. I need Correct and improve this database, specially for showtimes ...
1
vote
1answer
352 views

Using slash in MySQL database

I am using mysql with php Yii framework.When doing data modelling I came accross some doubt.I have a table for user details like this. CREATE TABLE IF NOT EXISTS `tbl_users` ( `id` int(11) NOT NULL ...
3
votes
1answer
1k views

Designing An ACL Based Permission System

I am trying to create a permissions system where everything is going to be stored in MySQL (or some database) and pulled using PHP for a project management system I am building.  I am right now trying ...
2
votes
1answer
532 views

Modelling an E-R diagram for a Pizza place database, need help representing the payment method for the customer

I am modeling a "pizza place" and a customer has an account info. I created a separate table for account info. In here a customer will have an amount spent and method of payment attributes. The ...
8
votes
3answers
6k views

Inventory database structure when inventory items have varying attributes

I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using ...
10
votes
4answers
8k views

What is an audit table?

What is the function of audit tables? How are they useful? I came across them reading this article.
5
votes
2answers
213 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
2
votes
1answer
107 views

Querying a big database

I am PHP programmer with basic-intermediate knowledge on databases. I am working on a project where I need to verify a given phone number's presence in India's Do not call database. Following is the ...
4
votes
2answers
924 views

Is my EER Diagram correct? Why use relationships?

This is the first time I've made an Entity Relationship Diagram (ERD) for MySQL. Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and ...
1
vote
1answer
482 views

DATETIME2 and IDENTITY as composite key

I am currently developing a new database schema with an opportunity to do things right. The purpose of the development effort is to collect data (events) from various sensors that will be recorded ...
6
votes
1answer
716 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
0
votes
1answer
470 views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
1
vote
1answer
676 views

How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook?

I'm new to databases and web development so a laymen explanation is best for the time being. I'm only in the data modeling phase so no need for specific code. I'm just trying to wrap my head around ...