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)

5
votes
1answer
326 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
87 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
139 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
84 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
159 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
386 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
369 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
321 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
304 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
354 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
291 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
231 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
647 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
353 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
540 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 are audit tables? How are they useful? I came across them reading this article.
5
votes
2answers
214 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
958 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
492 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
726 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
490 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
706 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 ...
4
votes
1answer
930 views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
4
votes
3answers
1k views

How do you structure a database that organizes post by location like craigslist?

I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to ...
5
votes
1answer
1k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
2
votes
1answer
69 views

Single field instead of several fields

There is a relational database with the following Users table User Id Name Age Parents Friends Colleagues ... And there are tables Parents, Friends, Colleauges, etc. Due to specifics of database ...
2
votes
1answer
135 views

Entity relationship many to many

Is an association class in UML the same as an Link entity in ERD?
3
votes
4answers
1k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
1
vote
2answers
125 views

Where does a type specific field go?

Let's say I have table of cars and table of car manufacturers. Each car references one manufacturer. Furthermore, each car that has manufacturer x requires an extra field. Say, cars from that ...
1
vote
1answer
217 views

Guidance on simplifying the following diagram

I have a case scenario where a customer can book accommodation or flight only or both. I have modeled the accommodation part, however i am making things complex by over-thinking how stuff works in ...
2
votes
1answer
194 views

How can i model this data

A customer can make a reservation for an accommodation, the accommodation can be in a resort or elsewhere. So i would say that a resort can have different types of accommodations, ie. Chalets, ...
7
votes
2answers
4k views

SQL Server: query performance (search 2 million rows)

Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much? ...
5
votes
2answers
140 views

Design for storing data only applicable to a small subset of records

So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients. Basically, we have a bunch of tables in some format similar to this (in ...
12
votes
4answers
513 views

How much of a security risk are published conceptual schema?

I was requesting the conceptual schemas from a government agency's information system for my research. My request has been denied on the grounds of it being a security risk. I don't really have ...
2
votes
1answer
2k views

DROP SCHEMA in db2

How do I drop a schema in db2 if it has got some objects in it? db2 DROP SCHEMA SCHEMA_NAME For some reason I am not able to drop the tables in the schema, so how can I force to drop the ...
2
votes
1answer
252 views

Oracle ash report

Recently, i have been handed over with an ash report from DBA. To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
1
vote
2answers
572 views

Database Design - Employee Clocking In and Out System

I want to develop Employee Clocking In and Out System (website). I am concerned two things: If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the ...