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

learn more… | top users | synonyms (4)

2
votes
1answer
149 views

How to make a table with fields that are other tables in MySQL?

I am making a music library system with php in MySQL. In this system there would be a table named Songs. Since many songs can be by the same Artist (Person), I wanted to store a table of Person that ...
3
votes
1answer
150 views

Combined or separated tables: File and Message?

Is this the best approach to design this table in the purpose of sharing the file online between my customer and I, or should I separate Message table and File table or combined them together? ...
8
votes
6answers
4k views

Best way to document database mappings for migrations [closed]

I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this? Excel is a very flexible way to document simple ...
4
votes
2answers
3k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
2
votes
1answer
202 views

Is dimensional modelling useful for modelling tables of calculations?

I have a set of tables that look a bit like this: Key 1 | Key 2 | Key 3 | Var 1 | Var 2 | Var 3 | ... | Var 200 ------------------------------------------------------------- X | Y | Z | ...
4
votes
1answer
625 views

Would appreciate some suggestions on how I should model this data

I am setting up a website that will have users. Users can search for songs and (if signed in) save them to a playlist or their library of songs. I want it to be like iTunes in that all the songs on ...
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 ...
2
votes
3answers
2k views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
1answer
383 views

determining user types upon authentication

I've got 3 different types of users (entities) that will log into a website in developing for a client. "FITTER", "CLIENT", "ADMIN" the first method I came up with was this. CLIENT TABLE | ...
4
votes
4answers
447 views

DB Optimization with 2 to the power of N - Approach clarification

This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know ...
2
votes
1answer
14k views

Multiple left joins or subquery?

I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites. I have the ...
1
vote
3answers
139 views

If A is next to B, how to automatically assert that B is next to A

I am working in Access 2010. Let's say I have a database that tracks where packages sit in a warehouse. Table: Packages PackageID (PK) PackageHeight PackageWidth PackageWeight PackageOwner (etc) ...
1
vote
1answer
312 views

Conditional one to many queries in mysql

I have a simple patients table and lots of Lab tests table. Every patient can have many Lab tests. What i want to do is to only query the tests which are already entered for that specific patient and ...
0
votes
3answers
669 views

SQL Server : primary keys advice to my whitepaper needed

I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should ...
1
vote
2answers
735 views

database normalization 2nf, 3nf

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps here is the initial table: ...
2
votes
1answer
860 views

Separate Schemas by Industry or by Application functionality?

I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web ...
4
votes
1answer
6k views

Rows with a variable number of columns?

I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter. I'm using code to fragment a tweet into words, usernames, and hashtags, so I'm meeting ...
13
votes
2answers
1k views

Schema-less/flexible + ACID Database?

I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be ...
1
vote
1answer
109 views

keeping database columns consistent with documentation

For a project, the client wanted to be able to add fields to any item and have them be searchable. To make them searchable, that would require the fields to take a column, but that would make the ...
1
vote
1answer
108 views

how to handle so much multiple value strings [duplicate]

Possible Duplicate: Multiple values in lookup fields i have a table has about 27 fields are just strings and every one of them have multiple values for example itemType Field have more ...
2
votes
1answer
461 views

Multiple values in lookup fields

I know newer versions of MS Access allow multiple selections in lookup fields. For example, if I'm collecting information on my students, I can have a field called Race with lookup values that ...
0
votes
1answer
1k views

Database design : multiple category of users login into same system

In our new system we have three type of people Business owner Assistant of business owner Staff (a skilled employee) I am having following entity [Business] - include detail about business ...
3
votes
2answers
2k views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
2
votes
1answer
95 views

Is it dangerous to have duplicated entries in a table?

My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this: Music_id | Music_title | Music_author | Music_link 1 Look at me now ...
8
votes
2answers
8k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
0
votes
1answer
258 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
8
votes
1answer
203 views

Relational database: in-RAM partitioning? Theoretical structure discussion

I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point ...
3
votes
2answers
186 views

Is there any better approach in designing this database?

CUSTOMER: CustomerID (PK) Name Email PAYMENT: PaymentID (PK) Amount Date PRODUCT: ProductID (PK) Type Name Price PURCHASE: CustomerID (FK) PaymentID (FK) ProductID (FK) Quantity Or ...
1
vote
3answers
318 views

One to one relationship or another approach?

I need some addition thoughts on how to approach a database design I'm working on. The issue is I have three related items... * Clan (has many games) * Game (belongs to a clan, has many teams) * ...
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
2answers
6k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
2answers
4k views

database schema for a product with multiple categories and hierarchical categories

I need some opinion on whether I am going in the right direction with this. I am trying to build a e-commerce site (with MySQL backend) where a single product can belong to more one than category ...
7
votes
5answers
10k views

Importance of ER Diagrams

I am a student and I am developing several projects as a part of my academia. While developing the database for one of the projects, we came across a situation where we thought about whether ERD is ...
5
votes
1answer
2k views

How to keep long multiple of questions and answers in database?

I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for ...
5
votes
2answers
4k views

What Is The Point of a Primary Key?

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than ...
2
votes
3answers
822 views

Physical Address Schema

I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked). Store the address ...
6
votes
2answers
473 views

Definition for “logical difference”?

I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
1
vote
1answer
181 views

Foreign key to subtype or super types?

I'm using SQL Server and in this case i have the following tables: Restock: Foreign key to an R&D Staff member R&D_Staff: subtype from Staff Sales_Staff: subtype from Staff Shipping_Staff: ...
2
votes
2answers
701 views

Generalisation in databases

I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c. Is it better to ...
2
votes
3answers
4k views

Sorting by DATETIME slower than by TIMESTAMP?

I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ? Because I noticed that ordering by numbered keys is done ...
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
2answers
2k views

6th normal form, Recomposition query, efficient implementation

I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the ...
1
vote
2answers
216 views

Designing an Accumulating Snapshot with Repeated Groups, need advise

Situation I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School. At the student level it has to show the trend of the student over ...
2
votes
3answers
263 views

Should I create a separate database table for temporary users

I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These ...
1
vote
1answer
2k views

User system database design

I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement/test. It basically consists of users, events, user profiles and a messaging ...
1
vote
3answers
6k views

how to Export a document contain tables name, columns names and column description from SQL Server 2008?

I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column ...
1
vote
1answer
646 views

Stock management - needs suggestions

I need to create a database which will have below modules: Items [Food items, stationary] Items Stock Management [stock in out] Customers Items Sale Management Issue: The system will have ...
0
votes
1answer
119 views

database normalization and optimization for postOffice DB

I am designing a web application for a post office. Performance in database is critical for them. I have designed the below structure for the post database, is there any flaw in my db structure which ...
2
votes
2answers
983 views

Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

We are planning to support for our application another rdbms (oracle). At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have schema ...
3
votes
3answers
723 views

Handling attributes that are time-variant in a Datamart

I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the ...