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 (3)

6
votes
1answer
471 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
238 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
436 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
93 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 ...
7
votes
2answers
3k 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 ...
17
votes
4answers
4k 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
315 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
837 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
400 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
2k 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
805 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 ...
9
votes
3answers
9k 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 ...
13
votes
4answers
12k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
5
votes
2answers
237 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
113 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
1k 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
665 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
884 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
560 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
1k 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 ...
5
votes
1answer
1k 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
2k 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
72 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
155 views

Entity relationship many to many

Is an association class in UML the same as an Link entity in ERD?
4
votes
4answers
2k 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
126 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
251 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
227 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
7k 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
161 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 ...
13
votes
4answers
553 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 ...
3
votes
1answer
4k 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
397 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
943 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 ...
1
vote
2answers
404 views

How to model double relationships?

Background: I have designed a solution for Filemaker to input data into a Question Database. After we are done with inputting data into Filemaker, we will be exporting the data as excel and ...
12
votes
2answers
3k views

Storing massive amounts of data from a sensor array

I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to ...
4
votes
7answers
32k views

How to export an ER diagram from an Oracle DB?

I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...
1
vote
1answer
103 views

Impact of changing schema name

I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1). Not withstanding any code changes ...
2
votes
2answers
749 views

Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)): staffid | target | ...
5
votes
3answers
699 views

how do I model this in database?

I am using mysql as my backend database. Here is the use case; A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
1
vote
1answer
104 views

Right Distribution of Databases amongst Multiple Instances

So it's obvious that many times you'll have multiple instances (even if on the same server) to have a logical separation of databases for security purposes. But when does it make sense on the ...
4
votes
2answers
564 views

Separate Address Table, Latitude, Longitude

We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each ...
1
vote
0answers
523 views

How to customize Place Order Functionality In Magento? [closed]

In Magento, when any customer buys product from front-end, then at last customer goes through order placing. According to my need, I have created one CUSTOM TABLE in Magento database. So my ...
0
votes
1answer
376 views

Database Design: What is the best structure for storing site offices and locations?

Background I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment ...
13
votes
4answers
9k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
8
votes
1answer
1k views

Schema designing for handling multiple payment gateways

This is more of a question that requires feedback. I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the ...
2
votes
1answer
348 views

Referring to super parent or as a separate entity?

I have this person table as super parent, id firstname lastname email telephone ... ... and user table as a child id person_id (FK) password username screenname ... ... They must be 1:1 ...
5
votes
2answers
2k views

how to enable temporal database capability in mysql.?

How to enable the temporal database compatibility in mysql database? Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...