Tagged Questions

Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (2)

0
votes
1answer
417 views

Which is the redundant relationship in this ER Diagram and why?

Which is the redundant relationship in this ER Diagram and why? A few of my friends and I have been trying to figure it out but keep going in circles. where the attributes of each entity are as ...
0
votes
2answers
59 views

This certain MySQL database organisation

Hypothetically, let's say that I have three tables: - Products (id, product_name, added_timestamp) - Individuals (id, name, last_name) - Companies (id, company_name, company_number, company_address) ...
0
votes
2answers
178 views

Mirrored tables on PostgreSQL

I have some values under "account" and I need to have the same values under "invoice", so, I'm trying to find a way to symlink my tables, so every time one gets updated, the another have the new value ...
0
votes
0answers
723 views

Help with Multi-Restaurant Menu database design [on hold]

I'm building an Online Food Ordering System with PHP and MYSQL, I need some help on the database design of the Menu. Please look at the design here Is the design good enough? To hold the menu's for ...
0
votes
0answers
65 views

How can I model this problem in a graph database?

I have a project that I'm working on where I extract data from PDFs and map/visualize the relationships between the extracted pieces. Here's an example of my problem: file: 11425646.pdf author: ...
0
votes
1answer
186 views

What's the practice to generate long user id like those on Facebook?

Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002). Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this ...
0
votes
1answer
598 views

User roles and permissions

Look at the attached graphic. I'm trying to keep this as simple as possible. Basically I want to have multiple admins(or superusers) who own regular users who own sites. Admins can give regular ...
0
votes
1answer
149 views

Relational database primary key requirements

I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business ...
0
votes
3answers
836 views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
5
votes
4answers
5k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
2
votes
2answers
144 views

Design consideration regarding state handling: how to store multiple, variable states for one entity [closed]

First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ...
0
votes
2answers
561 views

Storing dynamic data in NoSQL

I have scenario where I need to store unstructured data but the rest of my data is structured and relational. An example of the type of unstructured data is as explained below: User Type 1: How do ...
2
votes
1answer
908 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 ( ...
0
votes
1answer
110 views

Am I wrong in table design or wrong in selected index when made the table?

I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
2answers
191 views

Should numeric identifiers like serial numbers be stored as integers?

Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect ...
4
votes
1answer
476 views

financial transactions with and without commissions

I'm building a gambling website. All financial transactions from and to external sources (credit cards, money transfer systems) are subject to commission. Amount of the commission depends on the ...
0
votes
1answer
206 views

How to add custom sequence number in table which is going to be populated automatically

I have table something like this CREATE TABLE [dbo].[ado_test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [tip] [varchar](10) NOT NULL, [datum] [datetime] NOT NULL, [doc_number] [int] ...
0
votes
1answer
226 views

Database with insert data every 2 minutes

I have a site to develop into PHP and mysql where I take, with a cron, every two minutes 10 xml from a server parse each and insert data into my database. Well this data are simple string (maximum 10) ...
0
votes
2answers
915 views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
6
votes
4answers
696 views

PK as ROWGUIDCOL or use a separate rowguid column?

There's a long-winded debate going on here so I'd like to hear other opinions. I have many tables with uniqueidentifier clustered PK. Whether this is a good idea is out of scope here (and it's not ...
4
votes
1answer
209 views

What is Transaction Path Analysis?

I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my ...
0
votes
0answers
129 views

How implement MySQL unique index with two foreign key that one can be null?

I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null. For example cannot be repeated a null foreign key with the other foreign ...
1
vote
2answers
131 views

What is proper database design for a drupal table with an order field?

I am adding a custom widget to an instance of Drupal 6.x & MySQL 5.5 and came across a problem with updating rows. I have a table of recipe ingredients where multiple ingredients are tied to a ...
-1
votes
2answers
134 views

what is the best way to update 2 tables via data entry form in access 2007 database? [closed]

I have a database with more than 500.000 records, I am using Table_a to Accessing the files by the ID number,Name, Date, Address, Tel, ..... And then I used Table_b to archive files and locate the ...
0
votes
2answers
330 views

Two nullable columns one required to have value

No-Explanation Question: Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have ...
0
votes
1answer
893 views

Changing primary key from varchar to int

I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key. So I would like to change my primary ...
0
votes
2answers
128 views

How do you store tabular data in an RDBMS?

First: I really haven't got any idea how to google for this. If you got one, leave a comment. If I want to store arbitrarily large tables in a database, how should I set up my database tables? ...
3
votes
3answers
303 views

Relation to original tables or to existing linking table

In my database I have a table with different settings for my app. Each setting is in relation to a guest (table guests) and an event (table events). So basically each guest has specific settings for ...
1
vote
2answers
320 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 ...
2
votes
2answers
152 views

Record versioning and promotion

Let's say we have this hierarchy: -World --USA ---WA ----Seattle -----Downtown ------1st Ave -------945 1st ave ------3rd Ave ...
6
votes
4answers
559 views

How can a database be normalized when an optional field causes denormalization?

Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this: A ...
0
votes
1answer
40 views

dimension table with “like” condition

In an effort to scale some existing code, we are removing logic from CASE statements and putting it in dimension/lookup tables. Thus instead of CASE WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' is now ...
1
vote
2answers
168 views

Usage of foreign key when the referenced table data will not change

I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely. For e.g., the user is presented ...
0
votes
2answers
711 views

Is there a good reason to store big text data outside database?

I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb. This data is only used when loading single record (like article or blog ...
2
votes
2answers
4k views

MySQL Workbench connect to MS Access

I have a .mdb file on my computer that I would like to convert into a MySQL Schema and I have found that there is a migration tool within Workbench. However it doesn't seem to allow you to connect to ...
0
votes
1answer
63 views

3nf but waste more storage space

I have a table which has three columns: account, idNumber, and position. The account and idNumber are unique. Here is my thought to analyze and I don't know whether it is right or not. account is ...
3
votes
1answer
1k views

Database design: Two 1 to many relationships to the same table

I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and ...
0
votes
0answers
163 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
0
votes
1answer
41 views

Table Design for user-specific and user-agnostic criteria

I want to design a table that is general enough to accommodate an increasing number of achievements as I come up with new accomplishments to reward. Initially, I thought to design the table as ...
0
votes
1answer
257 views

Time series data for ad platform

I am trying to figure out how to store time series data for an ad platform I am working on. Basically I want to know some strategies/solutions for storing billions of rows of data so that I can ...
4
votes
1answer
466 views

Database table design question

Firstly, database novice here. I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able ...
2
votes
2answers
518 views

Recommendation for mySQL table structure (no code)

I need some advice on how to layout the tables for a mySQL database. I will first describe what I am trying to do: I have a table for users. (This covers basic info for now) A user will select a ...
0
votes
1answer
93 views

When is it worth it to use Triggers and stored procedures/functions? [closed]

As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post ...
0
votes
4answers
38 views

Are all matching indexes (indices) searched for a query?

If I have multiple indexes that include a common attribute - will each index be searched upon a query involving this attribute? Or is only one index searched?
1
vote
0answers
211 views

Database Design: separating reusable & instance data

I'm developing an app which is basically a collection of checklists that have to be processed. I obviously need a database in which the information has to be stored. I just can't wrap my head around ...
4
votes
1answer
275 views

Where and how to store my weird datamart

I could really use some help here. Here's my situation. I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
3
votes
1answer
92 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 ...
4
votes
1answer
103 views

One database or multiple referencing one?

Database design: one database or multiple databases, which is best? We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications ...
1
vote
1answer
131 views

Multi-variance products

My current system houses different variants of Products in different tables; Productgroups Productcolors Productsizes Productmodels Products can reside under 1 Productgroup and can have ...
1
vote
1answer
94 views

How to “swap” out production tables

I have a MySQL table that holds a few thousand records which go out of date very quickly. A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the ...