1
vote
0answers
43 views

How to handle many columns / variable schema?

I apologize in advance in case this question has been asked already. I'm working on revamping a reporting application used within my company. The requirements are: Support addition of new fields ...
0
votes
1answer
23 views

Max from 2 columns with priority date col then the seq col Oracle

I have a column xdate, column seqnumber, personID I want 1 record per personID, where it is the most recent xdate max(xdate) if there is multiple same max xdates i want it to look then for the max ...
4
votes
1answer
328 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
2
votes
2answers
406 views

Is there any way to do a SELECT query without any optimization in SQL Server?

I am trying to test the speed of SQL server queries. However, once I do the query once, it becomes a lot faster. The database is live so I can not make any changes to it, only to the SELECT query ...
3
votes
4answers
193 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
1answer
51 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
1answer
19 views

Optional oneToOne relations and criteria field

Introduction: Visitors can fill reports on my website. Those report can either be filled by a legalPerson or a naturalPerson. Constraint: I don't like empty fields and I would like to normalize as ...
2
votes
1answer
50 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
61 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
104 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
36 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
13 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
26 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
2answers
127 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
56 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
28 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
23 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
71 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
17 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 ...
0
votes
4answers
250 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
68 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
82 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
59 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
209 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
98 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
39 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
28 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
35 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
115 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
137 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 ...
2
votes
1answer
509 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
83 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
52 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
515 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
81 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
284 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
175 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
71 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
68 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
199 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
158 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
61 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 ...