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

learn more… | top users | synonyms (4)

3
votes
1answer
55 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
360 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
143 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
512 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
583 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
181 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
143 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
251 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
728 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
1answer
3k views

How to optimize multiple self-JOINs?

I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design. One of the tables looks as follows (relevant cols only): CREATE TABLE IF NOT EXISTS CountryData ( ...
3
votes
1answer
139 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 ...
3
votes
1answer
465 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
790 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
110 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
130 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 ...
3
votes
1answer
633 views

Applying user-defined fields to arbitrary entities

Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
3
votes
1answer
266 views

Economic Database Architecture Possibilities w/ Data of Varying Length

I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, ...
3
votes
1answer
595 views

Curious Database building Problem (ER Diagram)

I am trying to build an EER Model for a Autostore that has 5 locations and offers a range of auto products. They offer car repairs and roadworthy tests as a service also. I need to be able to make ...
3
votes
1answer
96 views

linking databases for reporting

I am attempting to design a simple reporting feature for some financial market data. I have a few sets of data, and I am trying to figure out what will be the best way to organize the tables, in ...
3
votes
3answers
462 views

An “unlimited” data model

I need to store an unknown amount of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All ...
3
votes
2answers
63 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 ...
3
votes
1answer
240 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 ...
3
votes
2answers
7k views

Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu) [closed]

Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ?? Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
3
votes
1answer
737 views

Do I have duplicate key indexes?

CREATE TABLE relations ( object_id BIGINT(20) NOT NULL PRIMARY KEY, object_term BIGINT(20) NOT NULL, UNIQUE KEY link(object_id, object_term), CONSTRAINT fk_term FOREIGN KEY(object_term) ...
3
votes
0answers
49 views

Personal Finance database review

G'Day, I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
3
votes
0answers
40 views

Online text based game design - Am I linking the databases correctly?

I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
3
votes
0answers
45 views

ERD for a Contract between a Writer and a Publisher: Is it correct, can it be simpler?

I created an entity-relationship diagram (ERD) for a book publishing business context that I will detail bellow. Entity types The entity types of interest are: Writer Work (as a book) Publisher ...
3
votes
1answer
36 views

Store the content book in database, so that it would be updated afterwards or store them as document?

I have some books in (urdu) made with Urdu Inpage(tool for making pages in urdu) like this: problem that these document files are difficult to read in php because they gives unread-able characters, ...
3
votes
1answer
36 views

How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?

I have a Parent entity type with two Child entities, with a relationship cardinality of many-to-one (or M:1) from Parent to Child. The business rules say that: It is mandatory that the Parent ...
3
votes
0answers
44 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
3
votes
0answers
50 views

Ranking items on a per user basis

I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one. All items are ...
3
votes
0answers
36 views

SQL Server storing predefined value sets along with open values

I have Cars with different Characteristics, Characteristics have values, which can be restricted by predefined set of values or be "open" values (any string). Characteristics and predefined values ...
3
votes
0answers
87 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color ...
3
votes
0answers
77 views

Is this user-storage database system efficient, effective and secure?

I am using: PHP Version 5.2.17-MIT-BACKPORT-GnuTLS SQLite 2.8.17 I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root ...