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

learn more… | top users | synonyms (4)

3
votes
2answers
337 views

Data Model For Summarizing Student Info

I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame. I could certainly go retrieve the count from the attendance ...
2
votes
1answer
124 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table called UserFriendList, which has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that said table holds the following data: ...
2
votes
1answer
97 views

Is it “OK” to have a table that only contain foreign keys?

In "OK", I mean "it doesn't have any bad impact with the overall database design". For example, will it cause insertion problems? or will it be a problem in the future when I add field in the ...
2
votes
3answers
204 views

Table naming and Database normalization

I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in. My current ...
2
votes
1answer
158 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in ...
2
votes
1answer
786 views

Personal finance database design

I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL. This system is currently using 4 tables: users, this table holds all the user ...
2
votes
1answer
305 views

Bicycle Sharing Scheme - ER Diagram

Please compare the directions below to my ER Diagram and tell me if anything is wrong with my ER Diagram Please I think its accurate enough. Oxford City Council is preparing to launch an innovative ...
2
votes
0answers
173 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 ...
2
votes
2answers
238 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
2
votes
2answers
278 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
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
826 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
74 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
189 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
457 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
2k 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
450 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
355 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
776 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
295 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
95 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
81 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
64 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
163 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
100 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
118 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
1answer
105 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
254 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
1k 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
161 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
214 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
252 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
282 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
651 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
563 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
200 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
780 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
354 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
2answers
37 views

Is one-to-one relationship required?

I have these three tables on my database: There is a one-to-one relationship between Code and Aggregation table (an AggregationId is always a CodeId. And of course, a CodeId is unique). Note: A ...
0
votes
0answers
20 views

Modeling question - Meta and instance

In a relational DB I'd like to model this: every customer_type has one module_set (1:1) a module_set has several modules (1:n) every module is different, and the user can activate them & reorder ...
0
votes
0answers
52 views

Normalized college database desgin

I am trying to create a college course database. The College offers UG and PG courses in various departments. Departments include program streams like MCA, MBA, and Engineering. MCA courses may be ...
0
votes
1answer
51 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
197 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
284 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
79 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 ...