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

learn more… | top users | synonyms (4)

5
votes
2answers
261 views

Web Database - How to store user information

I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a ...
3
votes
1answer
464 views

Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

I am not sure if this question has been asked or not. At least I couldn't find it. I am curious about a primary key in terms of efficiency with data searching and retrieval. This is a hypothetical ...
0
votes
1answer
3k views

Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

I'm working on a question/answer project and having a hard time coming up with a suitable database structure. Each question can have from 1 - "n" answers, and each answer is associated with one of ...
1
vote
1answer
3k views

MYSQL Social Networking website - Database Design, Data Growth and Hardware

Introduction I have a system around one million users (community based). It has around 6000 groups (fixed , no change). But there is no discussion board for these groups yet. Building discussion ...
1
vote
5answers
989 views

SQL Server - Database per company. How to query across databases?

We are designing a product that, as per the client's request, must utilize a database-per-company design approach with one shared database holding common data. We are using SQL Server 2012 for the ...
2
votes
1answer
2k views

Why is a Star Schema more normalized than a 3NF Schema?

According to Oracle's documentation, third normal form schemas "may require less data-transformation than more normalized schemas such as star schemas". I'm confused, I thought 3NF is the most ...
1
vote
2answers
286 views

Can graph databases be easily distributed?

I have never worked with graph databases such as neo4j, I would like to know if they support easily replication and clustered distribution as well as key-values databases, or if there inherent ...
4
votes
1answer
2k views

Modelling Relationships in an E-R Diagram, Table or Foreign Key?

Should a relationship in an E-R diagram be created in the database as a table or a foreign key? For example: a tutor lectures a class I see it as three possibilities: there is a "lectures" ...
1
vote
4answers
1k views

How do I develop this data model without a circular reference?

I am trying to develop a concept for a new call tracking system for a help desk. The top 3 levels reference each other. I am trying to determine a better way to link these tables, and am drawing a ...
4
votes
1answer
112 views

How do you manage a list for particular object. for eg. user comments on specific object(user post etc.)

I am new to database and web application development. I want to know how to store a list for a particular object in a database. For example, user comments for a particular post(object), or the ...
4
votes
1answer
2k views

Database Design for Questions and Answers

I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to ...
1
vote
1answer
3k views

How to design a table for storing news items?

I have a script that can get result from news websites and it will run every hour. The php script will get the title and content. The things I want to store in MySQL are: title content ...
9
votes
3answers
574 views

What should I use? A string or 15 integer fields?

I am developing a student tracking program where I need to store 15 exam marks. I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
2
votes
1answer
273 views

From a design perspective, what is the difference between an inventory system and an asset tracking system?

From what I can see, the database design is similar. One is the consumables that are resident in a warehouse (inventory). The others are items, capital, that are assigned to people or places, but ...
12
votes
2answers
1k views

Modelling constraints on subset aggregates?

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
6
votes
1answer
2k views

EAV structure explained in Layman's terms

I've read several articles online that explain how EAV structures work, but there are different interpretations of them. Some articles mention that it's a representation of a data model metadata (or ...
-3
votes
1answer
314 views

Compare MongoDB and MySQL

Can anyone help me to make right decision for choosing scalable and reliable database? I have to create 3-4 tables with around 200 columns each and these tables will have around 5000 data records. As ...
3
votes
3answers
372 views

Database design for changing number of fields

Google Contacts lets you to enter more than one phone number, or email address for a contact. You can add as many as you want. I don't know if there is a limit, other than practical list size. To ...
4
votes
2answers
665 views

Many to Many Relationship Question

I have a database, with one main table: Project, and about 10 other tables (Entity1, Entity2, etc.) Each of the different entities can belong to one or many projects. I know I can have tables like ...
1
vote
0answers
278 views

Improving database design for category based site

I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
0
votes
1answer
263 views

How to simplify schema for multi database / hundreds of millions of rows data set

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started ...
7
votes
2answers
494 views

Data Compression Through Base36 Encoding

I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type. I'm thinking about using Base36 encoding to accomplish this. Are ...
4
votes
1answer
3k views

Proper tuning for 30GB InnoDB table on server with 48GB RAM

I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
5
votes
2answers
1k views

Vehicle spare parts inventory datadase model

I am modeling a database to be used for vehicle spare parts inventory management and I am stuck... Some spare parts can be broken down from a kit into component parts. I would like to create a model ...
2
votes
1answer
500 views

MySQL table locks solution -> InnoDb / Partitions

I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows). It's mainly caused due many heavy inserts. My current thoughts to optimize: Changing to ...
1
vote
1answer
451 views

MySQL table locks solution -> InnoDb / Partitions

I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows). It's mainly caused due many heavy inserts. My current thoughts to optimize: Changing to ...
-1
votes
1answer
327 views

Database for an application similar to Chatroulette

I am making an app that is similar to Chatroulette. When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could ...
4
votes
4answers
14k views

What's better/faster? MySql or FileSystem?

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography. I'll admit my SQL queries could be better but in general what would be faster and ...
2
votes
1answer
406 views

Schema for 1 <-> 0..1(+), many related but only one primary

Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My ...
2
votes
1answer
966 views

How to set unique columns combination

see this table: CREATE TABLE IF NOT EXISTS `default_messages_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `owner_user_id` int(11) NOT NULL, `to_user_id` ...
1
vote
2answers
1k views

Database design experienced view

I want to make a database that stores countries, their respective states, and cities. I am confused how to normalize these. Can anyone help? My ideas: Table 1 Countries (id pk, name , code) ...
4
votes
1answer
246 views

How can i prevent decryption in SQL Server procedures

I came across in internet tool to decrypt procedures, I checked my database with this tool to decrypt my procedures and it works fine, now the problem is I wish to prevent this kind of tools from ...
1
vote
1answer
3k views

How do I perform a UNION query on a dynamic list of tables?

My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>. Each of these tables has the same data structure. I also have another ...
12
votes
4answers
2k views

Reason to not use nullable number in Oracle?

Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel ...
6
votes
2answers
2k views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
1
vote
2answers
911 views

Single or multiple databases?

I am in the progress of designing a database (or databases) for somebody and since I am a beginner I would love some advice. The data is described as: Customer1 (c1.type1, c1.type2, c1.type3) ...
10
votes
2answers
8k views

Designing a user authenication (Roles & Rights) module

I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
3
votes
2answers
150 views

Which tables to create according to relations

We are planning a new system with a new DB (MS SQL Server). We have the following entities and the following relations: Entities: Clubs, Chains, PaymentTypes, etc... Relations: Clubs-Chains (M-M); ...
3
votes
2answers
196 views

Large scale, hard to model design advice needed

I would love to hear some opinions or thoughts on a MySQL database design. Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of ...
4
votes
5answers
3k views

Does 'Cities' deserve a separate table?

I have the following Customers table: customer_id - int company_name - nvarchar street - nvarchar city - nvarchar comments - nvarchar The app will only be used in part of one small country ...
3
votes
2answers
759 views

How do I create a user invitation model?

I am developing an application which allows 'users' to create 'lists'. I have a many-to-many relationship between 'users' and 'lists' (this might not be right). Additionally 'Lists' have many 'Tasks'. ...
4
votes
2answers
420 views

Database modelling best practice question

I am modelling a database for an application I am developing and I am in front of a choice for which I would like to know what the best practice is. Let's say I have a Documents table and a Users ...
4
votes
1answer
735 views

SQLite3 simple database design question

I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database. The ...
3
votes
1answer
152 views

Architectural Question about Data analytics

I understand that in many OLTP architectures there is some ELT stuff which creates a OLAP Data warehouse model and then you can run data analytics on this. However, this can take some time to ...
2
votes
1answer
110 views

What are practical hurdles in making protein interaction database?

Following are some protein interaction databases: http://string-db.org/ http://dip.doe-mbi.ucla.edu/dip/Main.cgi As a newbie what are the practical aspects of database that I must understand before ...
3
votes
3answers
491 views

Is this database fine for calculation?

I am doing an application called as Invoice Application.For that I have my database is like this. Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
2
votes
3answers
351 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
4
votes
2answers
903 views

Designing an aggregation schema

The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it ...
5
votes
2answers
97 views

Per person weighted answers

Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits. My question is I have a set of people for which I want to record needs mapped to ...
2
votes
2answers
302 views

Writing to a DKNF database, but reading from a de-normalized table for performance?

Would the following approach be reasonable? The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database. The Context: A database for ...