The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

3
votes
2answers
612 views

multivalued weak key in ER database modeling

I was wondering since i didnt find out any clarification for this. I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as ...
3
votes
1answer
1k views

How to design tables for players in a tournament?

I need help with my database table design. I have group which contains a list of players. In every group each player plays games with other players. Game has also some result for example 1:0 and ...
3
votes
2answers
10k views

How to make a trigger that will compare input with a value of other table?

I had made two tables with the following fields: TABLE 1: "Bookings" FIELD: "fk_flight_number" FIELD: "date_of_reservation" TABLE 2: "Flights" FIELD: "flight_number" FIELD: "...
3
votes
1answer
431 views

Best mysql db design to solve timezone issues

I faced problem with timezone on my last two projects. So What is best way to sort out problems with timezone on db design. I mean, What format should i added date time stamp. On of my problem. My ...
3
votes
1answer
6k 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 ...
3
votes
2answers
3k views

What is the right time to use an ENUM versus a normalized types table in MySQL? [duplicate]

Possible Duplicate: Advantages and Disadvantages to using ENUM vs Integer types? Considering MySQL only... I'm curious to know what the community thinks the proper use-case for an ENUM type ...
3
votes
1answer
1k views

Webapp & MySQL: Row Level Security

I am trying to emulate row level security on a webapp I am developing using MySQL. Using this method: Creating a database with the the required tables where the data pertaining to all the users will ...
3
votes
1answer
142 views

Does document “depth” affect CouchDB performance?

I have a project where it is very tempting to use a deeply nested collection of data. But how does that affect performance? Are there any reasons why I should avoid doing this? I'm talking about lists ...
3
votes
1answer
176 views

Mysql constraint problem

I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this: +------------+ +------------+ +------------+ | t1 | | t2 | | t3 | ...
3
votes
3answers
1k views

Priority Inbox Database Design

I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox? If you're unfamiliar with the system, it prioritises messages based on the type of ...
3
votes
1answer
50 views

Use large set of data in multiple databases

I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter. Say you have a large set "X" of data that consists of multiple tables. For the sake of ...
3
votes
2answers
68 views

Is it possible to display this restriction in an ER diagram?

I have a question about modeling a particular kind of restriction, but I don't know the technical term for it, so I will illustrate it with an example. I wish to make an ER diagram for a hotel room ...
3
votes
1answer
238 views

Versioning data in a database [duplicate]

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
3
votes
1answer
53 views

Seeking feedback on table relationships, general schema design

I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL. Here's what I have so far, link to full size image I'...
3
votes
2answers
238 views

Many-to-many relationship when one entity is without attributes

I'm interesting in mapping many-to-many relationship to relational model. I understand that the basic approach is to create a third table (associative entity) that connects the entities in many-to-...
3
votes
4answers
3k views

Calculate row size and max row size for a table

Problem: Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate ...
3
votes
1answer
110 views

Inventory management in Rails

I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
3
votes
1answer
357 views

Supertypes and subtypes

I am trying to design a database that has supertype/subtypes. I have the following users: - basic user - business user - administrator (super user). All three users share a common table (users) ...
3
votes
1answer
144 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
3
votes
1answer
56 views

Should I create a extra relation-table when I have two columns in one table referencing to one column in another table

My problem is best explained by example: Suppose I have a database about chess games and players. Which one of the following structures should I choose Option 1) player (player_id,name... etc) ...
3
votes
1answer
1k views

Rich text: VARCHAR, TEXT or BLOB in MySQL

Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or ...
3
votes
2answers
383 views

Database design for multiple user attributes

I am building an app with Laravel, and I have users which also have a profile. I am unsure about the best way to set up the database for the user attributes. For example: user_ud | name | ...
3
votes
1answer
147 views

How to create a Relation between a table and a many-to-many relation table

I am buildng a new database inside an Sql server 2008 r2.I have the following tables & business rules:- Skill that have predefined levels assigned to it, such as junior, senior, manager. Staff ...
3
votes
1answer
67 views

Best Design for this “Sweepstakes” Project?

Hello there fellow developers, Merry Christmas first aff all, and i hope you will have a happy new year soon. :) I was having more or less a problem with an idea that came to my mind. I am currently ...
3
votes
2answers
57 views

I have this two models, which one is better?

what is a better design?? Model 1: +------+ +--------+ | type |-|--+ | Data | +------+ | +--------+ | id | | | Mach ID| | type | | | Time | | len | +----&...
3
votes
1answer
534 views

Building a branched versioning model for relational databases

I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions ...
3
votes
1answer
601 views

Optimum design / structure for product data database?

What would be the optimum design/structure for product data database, where each product have many variations and many attributes? Attribute sets vary between products, and some attributes have values ...
3
votes
1answer
1k views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
3
votes
2answers
255 views

How to make a better Data model

I am creating a database system using MySQL for a group in my school (a University). I am new to MySQL and its usage.I have started with the data modelling process, since this process will be the main ...
3
votes
1answer
1k views

Designing a reservation system schema

I am not sure if this has been asked before, however I am tasked with designing a new reservation system for my organization We have an older system which simply stored the booking in a table, and it ...
3
votes
2answers
182 views

Recipe Health risk Database ERD structure

I have these tables: First table(Recipe table), Recipe recipeid(PK,AI) recipename ingredients Second table(Ingredients table), Ingredients ingredientid(PK,AI) ...
3
votes
2answers
57 views

Calulating savings between two schemes

I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same ...
3
votes
2answers
64 views

Best design for a many:many relationship issue

We have three types of entity, each with their own table: table:A(a_id), table:B(b_id), table:C(c_id) Each of these entities can have any number of email addresses and linked Twitter accounts. ...
3
votes
1answer
77 views

Which items are table entities and which are fields?

I'm a mystery novelist with a series of three books that will end up at around 20 novels. I am having trouble getting the tables and fields sorted out. I want to be able to: create a list of all ...
3
votes
1answer
145 views

Sql schema design advice

This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database. So recently I started designing an application ...
3
votes
1answer
252 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
3
votes
1answer
741 views

Learning to draw entity relational diagram

I am trying to learn entity relationship diagram, following question is the objective. Information consulting firm has number of offices with employees. The employees can move offices depend on the ...
3
votes
1answer
106 views

Constraint to one of the primary keys as foreign key

Table1: grid_col (col_id,f_id,f_value) (col_id,f_id) is primary key. Table2: grid (grid_id,col_id,text) (grid_id) is primary key. I want to have a constraint for grid that, col_id ...
3
votes
2answers
94 views

Which design to choose in this case?

I am designing a database for a video game. I have some data structures like {x, y, z, dimension} used across multiple tables. For instance : Table player_death id: int player_id: int death_x: int ...
3
votes
2answers
176 views

Design consideration regarding state handling: how to store multiple, variable states for one entity [closed]

First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ...
3
votes
1answer
144 views

How to share resources among accounts?

Using google doc, I am able to share my documents with others. I wonder how they implement it underlying in terms of DB design? The simplest way I imagine is to use a joining table which keeps a many-...
3
votes
1answer
468 views

SSIS Design consideration with object variables

I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation ...
3
votes
3answers
815 views

How can I improve my table design for different types of an entity?

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables ...
3
votes
1answer
113 views

Does avg_query for PgBouncer include update and inserts?

Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage: avg_query Average query duration in ...
3
votes
2answers
8k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
3
votes
1answer
309 views

How to avoid Repetition in the Database Design?

I have currently the following database structure, Retailers ----------------------------------------------------------------- ID Name XXX XXXX XXXX XXXX XXXX XXXX ...
3
votes
1answer
1k views

Payment methods conceptual and logical model

I need to create a conceptual and logical (normalized) models of parking house according to the requirements below. It looks to me as a very simple concept that doesn't need all tables to have ...
3
votes
1answer
136 views

which design better when use foreign key instead of a string to store a list of id [duplicate]

Possible Duplicate: Is using multiple foreign keys seperated by commas wrong, and if so, why? I'm building online examination system. I have designed to table, Question and GeneralExam. The ...
3
votes
1answer
131 views

Help to create indexes to improve query performance and reduce report generation time

The database in SQL Server 2008 has these tables below - the main table being the Master: Master A_ID (PK) SYSSerialNumber Bootlog SequenceNumber FileName Cfg Unique(serialnumber,sequencenumber) ...
3
votes
3answers
3k views

Empty LONGTEXT or NULL?

In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use ...