2
votes
1answer
40 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
0
votes
2answers
52 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
0
votes
1answer
90 views

Database revision needed for sports events database

This is not a code review but more of a database schema review. I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
1
vote
1answer
34 views

pass data from deleted table to another

This is the situation: I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns. TABLE_1 has records, but I need to pass ...
0
votes
0answers
11 views

A better database model for level of product-information in service and maintenance environment

This is a tough question and rewriting it now for a couple of times. I have a question about a good, more solid way to design my database model about service and maintenance. My main problem are the ...
1
vote
2answers
20 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
1answer
71 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
-1
votes
1answer
42 views

database normalization to 3NF [closed]

I am just at a complete lost. I need help with this. There should be 12 tables (or perhaps 11 since one table has just a single attribute) when you are finished with the normalization and merging of ...
0
votes
2answers
27 views

Design for table with multiple links to other table

I am designing a database to keep track of our network equipment. My db has 2 tables: sites & circuits (circuit = cable) & (site = property location) Each circuit runs between two sites, so ...
0
votes
0answers
14 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
0
votes
0answers
34 views

eCommerce product table design - MySQL

I am designing database for an web application, I have designed the product and specification details database as follows, create table pr_size ( size_id int not null auto_increment, ...
0
votes
0answers
15 views

Redesign tables, multiple foreign key and related table to same table

Hello I have this database: Table Teams is used in each one table. Match table contains teamA and teamB and round contains team that win the SET and team that start the set. But I dont know how I ...
-1
votes
4answers
218 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
0
votes
1answer
51 views

Best relationship database design to manage types for different entities

Suppose we have to manage phone types for: Clients, Employees and Suppliers. Each of these can have multiple phone numbers, so for better management I would like to add phone type. Each entity can ...
1
vote
1answer
60 views

What is a good/healthy mysql query execution time?

What is a good/healthy mysql query execution time? To my calculations: The site gets 1000 unique visitors per hr 10 page view for each unique visitor 5 minutes for each unique visitor ...
0
votes
1answer
46 views

Database design Problem for the web-app

Hello I am in a problem of designing a web-app database schema. Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like ...
2
votes
0answers
48 views

“Wide” vs “Long” Database Structure - Is there a logic when to use which? [duplicate]

I'm currently setting up a database to house records for an application I'm writing and am trying to come up with the most efficient DB structure to use... Of course I know that there are as many ...
0
votes
1answer
32 views

SQL - Query on table creation, referential integrity

Please let us know what does the below definition means on table creation. CREATE TABLE A(w INT PRIMARY KEY); CREATE TABLE B(x INT PRIMARY KEY REFERENCES A(w) ON SET NULL) As per my understanding, ...
2
votes
2answers
205 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 | ...
0
votes
2answers
80 views

What is the advantage of using numeric primary keys in a relational database?

Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to): create table widgets ( widget_id number ...
0
votes
1answer
35 views

Schema for dynamic number of attributes

I am implementing an application where users can save alerts for different services. Each service can have multiple types of alerts and each alert has X number of attributes to form a predicate. So ...
0
votes
0answers
27 views

Enterprise system database strategy [duplicate]

Here is my current database design: Separated databases based on area of domain (Sales, HR, Accounting, Core such as login and menus, etc). Each database has a single service login, each with ...
0
votes
0answers
34 views

more queries vs storing results in database

Environment: Embedded (smartphones) Data Structures: Object Table A: id, field1, field2... Object Table B: id, field1, field2... Object Table C: id, field1, ... , Object A, Object B ... many ...
0
votes
1answer
62 views

Which one is correct and optimized in sql?

Imagine that I have three entities (EntityA, EntityB, EntityC) that can have some images. So there are two ways: Make an image table for each entity. It means that EntityA has a image table named ...
1
vote
1answer
38 views

UK Addresses in 3NF

I am in the process of creating a UK only database which holds information for various sets of people i.e. Staff, Clients etc. The tables for each of these sets of people have an Address, City and ...
1
vote
1answer
108 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
1
vote
2answers
117 views

Implementing voting system like reddit/StackExchange?

In creating an application that would require a voting system similar to that of StackExchange or reddit, I want to know how the database tables should optimally be structured. So far, I have ...
0
votes
1answer
256 views

Database design for eCommerce website with multiple products having huge specifications index

I am designing a web site for online shopping and I have a question related to that. As you know that shopping website may have different product with different statistic/specification. What is the ...
0
votes
0answers
72 views

How to increase application performance with Centralised SQL Server [closed]

I’m trying to get my head around to make better way to increase the performance of my application System based on single SQL Server with 50 clients tablets pc(its connected to local wireless). my ...
0
votes
5answers
1k views

Is it impossible to store 2^266 records on a database of some kind? [closed]

Each records weighs 52 bytes, is it possible in today's technology if someone had the storage needed? What kind of db would hold the data and will make it possible to retrieve it. An example record: ...
0
votes
0answers
49 views

Reduce read write delays in specific database setup

I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that ...
0
votes
0answers
18 views

Performance One Row With Total Vs Multiple Rows with Seperate Values?

Not 100% sure how to explain this situation but here it goes: I am building an Inventory Database that all items received are entered into and a label is created for each Container, that is going to ...
1
vote
6answers
394 views

Efficiently storing irregular/repeating intervals (think calendar/events)

I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing. Right now ...
0
votes
0answers
62 views

Alphanumeric field in MySQL

I need to create a character field with some rules, they are, Field name: contract_number. Rules: Contract Number Contract number must be alphanumeric. Contract number must be unique. Contract ...
0
votes
0answers
200 views

Conceptual data model for a billing system

I am working on existing MySQL database, my task is to create a new data model for a billing system on that existing application, I have created a conceptual data model, as given below, My ...
0
votes
1answer
44 views

SQL tables relations

i'm making a MySQL data base , my tables are: users, activitylog, activitytype. to avoid repetitions i thought about including activitytype in each activity which is logged into activitylog table, ...
0
votes
1answer
140 views

MySQL DB structure with Main category and several nested sub categories

I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, ...
0
votes
1answer
69 views

Beginner table design

I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I ...
1
vote
0answers
59 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
1
vote
4answers
168 views

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small ...
2
votes
1answer
70 views

Database Design guidance required

I have scenario where there is Table T with columns PK,A, B, C,D TABLE t ( PK, A, B, C, D ) i have to insert data from different sources ie columns A,B,C,D are calculated separately.. and they same ...
1
vote
1answer
144 views

Autoclose property resets to true after attaching db even if model is set to false

I am using SQL Server Express 2008 R2. The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I ...
0
votes
2answers
58 views

A simple database and Query System? [closed]

I am new to databases and trying to solve one problem in which I have to build a simple database having few entries and then querying that database and then recording that answers from the database. I ...
0
votes
1answer
1k views

Float datatype with 2 digits after decimal point

In SQL server float datatype does not display leading zeros after decimal point declare @num as float set @num=5.20 select @num will dispaly only 5.2 But i want it to display as 5.20 After a ...
0
votes
1answer
508 views

Inventory Management DB Design

*strong text*I am putting together a small inventory management system and have some questions concerning the database design. What I was planning on doing was to have 3 tables, items, bins and ...
0
votes
0answers
56 views

Same table for real persons and fictional one

In my application stores both real and fictional persons (from movies, etc.) as well as stage names and alter egos. These entities have almost the same properties and are often referenced via foreign ...
6
votes
1answer
173 views

Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme

I'd like to know if it is better to use a RANGE RIGHT partition scheme or a RANGE LEFT partition scheme? Is one of these options by default a better choice, or is it heavily dependent on the actual ...
1
vote
0answers
123 views

KPI Metrics Table Design [closed]

We are currently working on project to get KPI metrics from out account partners,We want to design a table and It will have 3 Major Business units 4 Business Verticals (Service Desk, Knowledge ...
3
votes
1answer
84 views

Sql schema design advice

This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database. So recently I started designing an application ...
4
votes
2answers
90 views

StackExchange clone: where should I add my indexes?

I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal? Here is the schema in Rails format (SQL format below as well): ...