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

learn more… | top users | synonyms (4)

2
votes
1answer
3k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
2
votes
1answer
792 views

counter table in mysql

i was going through the book: High performance MySql 2nd edition. In chapter 4 it says: An application that keeps counts in a table can run into concurrency problems when updating the counters. ...
2
votes
1answer
73 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
2
votes
1answer
183 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
2
votes
3answers
449 views

Database design for conditional detail table

I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables: ...
2
votes
1answer
1k views

Foreign Keys with Composite Key

Imagine I have a table with the following dependency: (Name, BoughtFrom, TimeBought) --> Cost Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
2
votes
2answers
444 views

Please review my first thought-out attempt at a database schema

I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL. I would like, if someone has time, to review my layout and ...
2
votes
3answers
349 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 ...
2
votes
3answers
758 views

File name as primary key?

I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key. For instance... "someimage.png" as the ...
2
votes
1answer
293 views

MySQL data modeling help

I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach. One or more organizations can sponsor an event or ...
2
votes
2answers
1k views

How do I properly design a many-to-many (charges/payments) accounting system?

This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance. Basically I'm having trouble ...
1
vote
1answer
93 views

What to name a field that includes Store and Version ID

I am building a database that manages the definitions for a POS system (menu items, cashiers etc). It will include many stores and many versions. So, typical table will look like this (MenuItems ...
1
vote
1answer
71 views

Data structure design for supporting arbitrary number of columns in table or database [closed]

I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data. The task I am currently struggling is: user needs to ...
1
vote
1answer
56 views

Apply Definition of MVD (multivalued dependency) to Example?

Supposedly this multivalued dependency (MVD) and this functional dependency (FD): {W} ↠ {N,D} and {W} → {N} hold in this table: V W N D ================= | v | w | n | 1 | ...
1
vote
1answer
159 views

Database schema for entities with two possible owner/parent types?

I'm using PostgreSQL with Sequelize as my ORM. I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
1
vote
2answers
97 views

Is this db/table structure sensible?

I'm not very experienced in database design so bear with me. I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I ...
1
vote
3answers
113 views

designing a database with type and sub type

I am trying to build a publications database, and i want to develop a genre relation that has various (maybe look up tables) sub-genres attached. as an example, in the genre relation there would be a ...
1
vote
0answers
140 views

Finding missing_index_details in MySQL

MS SQL Server apparently can tell you what indexes would be useful for your query. Is there anything similar in MySQL? This article (scroll to "Find missing indexes") says that in Microsoft SQL ...
1
vote
1answer
104 views

Different kinds of pricing PostgreSQL

I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this: +----+------+-----------+-----------+ | ID | Name | price_old | ...
1
vote
2answers
251 views

Relational Model vs. Database Products [closed]

The formal Relational Model (logical level) for databases is not exactly the same thing as you get with MySQL, Oracle or other major products / implementations of ‘relational’ database management ...
1
vote
1answer
962 views

Multiple User Types - DB Design Advice

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. ...
1
vote
2answers
1k views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
1
vote
2answers
149 views

I don't know how to read many to many relationships

I'm reading this tutorial, and I have question about it. Look at this picture: Looking at Physical Many-to-Many Implementation I read the relationship this way: 'A user can be in zero or more ...
1
vote
2answers
211 views

Why do we not store names in a separate table?

Practically, I would never store names for customers in a separate table. But why not? It's a one to many relationship (a person can have one name but that name can be used by multiple people). ...
1
vote
1answer
49 views

Shared emails in a database [closed]

We lost our database administrator over a year ago - and even then she was promoted into the job internally and sort of learned on the job. Basically I say this to establish that strong data standards ...
1
vote
4answers
244 views

Better Indexing for MySQL?

Is it better to index each column individually with it's own key or to index multiple columns under the primary key? I've seen it done both ways in many different applications and I don't see a clear ...
1
vote
3answers
267 views

One or several tables for attributes

I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something. The overall goal is to create the ...
1
vote
2answers
637 views

Database model: doubly linked list

I am not sure if I get this right. I'm designing a database model for use in an application, and I'm stuck at a doubly linked list (model first; there is no code yet). There's an order. An order has ...
1
vote
1answer
552 views

designing server side database to store user-specific data

I have a database with a number of quiz questions and answers. My basic design consists of these two tables questions (question_id, question_string); answers (answer_id, answer_string, ...
1
vote
1answer
199 views

Splitting a table based on business logic

This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach. Typically, I break database design into two categories, Business Logic and Recorded Data. ...
1
vote
1answer
765 views

Implementing Pub/Sub initiated from the database

I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is ...
1
vote
2answers
352 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...
1
vote
1answer
119 views

Are there situations where queries are formed geometrically?

The topic came up in this other question. We were discussing sign value on a primary key having any significance. If there are good times to use geometry in building queries the sign value could be ...
0
votes
1answer
48 views

Split data across databases/machines

I have two questions about how to partition data across servers. Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third ...
0
votes
2answers
52 views

Does a record with NULL data does not eat any storage on the harddrive?

This is not an expert question about database, so I'm not sure if this question can be posted here or not. So if this question is out of place, feel free to close it. It's a long development story, ...
0
votes
2answers
176 views

Multiple rows v/s multiple columns

My requirement is as follows: An app with internationalization support and starting with EN and FR versions of text. So, I have 2 approaches for data in the table: 1. One column for each language: ...
0
votes
2answers
256 views

Many tables v/s one table with different entities types

My requirement is as follows: App with internationalization and mobile v/s desktop app text support. So, I have came up with below columns in the table which I will certainly need: "CODE" ...
0
votes
1answer
76 views

Load & traffic balance best practices

I am about to design an enterprise solution for a large scale application in which a windows service, MVC, and many other projects will use a database, so I am thinking about load balancing and best ...
0
votes
1answer
80 views

EAV table question

What's the best design for taxonomies in EAV tables? taxonomy id autoincrement name unique terms id autoincrement tax_id references taxonomy(id) name text unique value text ...
0
votes
2answers
247 views

Normalising shared entities and applying constraints

I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many. For example, one may have the generic entity ...
0
votes
2answers
209 views

Design a Lab Report tables [closed]

I need to design tables for a laboratory test. I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update. Also each ...
0
votes
4answers
124 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
0
votes
2answers
421 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
40 views

String templating technique

I am working on a fitness app that is going to deliver notifications to the user. I intend using MySQL events to populate the necessary tables, and then using an Ajax timer or Websockets or whatnot to ...
0
votes
1answer
1k views

Benefits of using Staging Database while designing Data Warehouse [closed]

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly ...
0
votes
2answers
943 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
0
votes
2answers
138 views

Configuration Table

Can someone explain what is the difference between a Configuration table and a Data table in a database? And how to design or implement Configuration table? I'm using MySQL and the requirement is to ...
0
votes
1answer
70 views

What do you call numerical coding systems that are left-aligned?

I'm writing an importing mechanism for the NAICS database. I have a few questions about this code-format. I've seen it before and I like it the setup. I'm going to ask some other questions about ...
0
votes
2answers
152 views

To normalize or not

I'm scraping data from a website and they have pages for Users and Groups. The pages are pretty similar; id, name, description, created date, number of elements and custom background. They also have a ...
0
votes
3answers
204 views

Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

I need to extend my existing table with status column. I am in doubt how to do that in most appropriate way regarding of future use of that status and performances of existing table. My table is ...