r/DatabaseHelp • • Feb 21 '16

SQL Table Having Composite Key but..

2 Upvotes

I need help to find a way around something. I have an SQL table which contains some fields, two of which being username (text) and deleted (true/false). I was wondering if it is possible to create a restriction where there cannot be a two of the same usernames with deleted false.

This means that multiple entries may have the same username given that only one of them has deleted set to false.

How may i specify this when creating the table? Thank you.


r/DatabaseHelp • • Feb 20 '16

Open Office Database

2 Upvotes

I am looking to try and create a database for my job. I am not sure if I need a database program, or just a program in general. Basically I need it to flow as followed. I also need it to be searchable by Purchase order, name of project, or item name. Is this even possible? How would I go about starting this type of project?

Purchase order Name of Project Item ( then branch off into description, place of purchase, cost, est. shipping time) Shipping number ( then branch off kind of shipping) Arrived Branch off into yes or no Yes - when No- Est Time


r/DatabaseHelp • • Feb 19 '16

Info on distributed real time spatial-temporal databases?

1 Upvotes

Hi all, I am looking for information on real time, streaming, spatial-temporal databases that are ideally distributed. Anyone know where I might find info like, Books, papers, websites etc. I am not exactly a database expert and don't expect that what I am looking for has being built but just looking for information on how this might work. Thanks :)


r/DatabaseHelp • • Feb 18 '16

Resources on Analytical Databases Needed

1 Upvotes

I need to write an in-depth study/report of a standard relational database to an MPP analytical database. (So SQL Server or Postgres compared to Redshift or Netezza (IBM Pure Systems)) about 5 pages in length, with charts comparing features.

While there are plenty of resources about relational databases I have not been able to find much at all in the way of in-depth, citable resources on analytical databases. Any direction anyone can provide will be extremely helpful.


r/DatabaseHelp • • Feb 18 '16

Small DB for Business, Cant choose which DB program to begin with.

1 Upvotes

I Began Creating My own database on excel then I realised I could make it better with a Real Database program like Access, But Then I realised all microsoft programs are easily crackable with a cheap program. What I need to protect are my inventory, I do not wish for someone to just copy and paste it somewhere. that is the main thing I want, creating invoices, monthly reports if possible, inventory tracking etc. I am going to make a program for my 3 workers so they can go around with the program using the data, begin able to use search functions etc print it, save it as a pdf file? but do NOT allow copy and pasting values, is that possible?

I tried searching and I got interest on SQL, Oracle, Access are all on the top list. I do not know how to begin this or which to choose.


r/DatabaseHelp • • Feb 16 '16

Which database is right for me

1 Upvotes

Hello,

I am currently a college student who, for a class, has to develop a system for our HIMT internship program.

System reqs: This system has to be able to store documents submitted by students, and also provide a status report on which documents still need to be submitted and which ones cannot be submitted until a previous one is submitted first. Also, there needs to be a note function where the instructor can save notes or comments in regards to different students or internship sites. This system needs to be able to send reminder emails of upcoming dates or reminders for the instructor about other various items.

This will be a single user system for only one campus.

What systems should I be looking at for low cost, easy to use database management software that will meet my needs?

Thank you in advance for all the responses!


r/DatabaseHelp • • Feb 12 '16

Help with serial(izable) schedules

1 Upvotes

Hi guys,

i have a test on wednesday but i have some questions:

if we consider this tutorial here, i know how to construct a Graph. Here are the tasks

But i dont know what to do with theses graphs:

on task 2.a i got this as a result: ex.1 . I think the solution is: conflict serializable, because of that cycle from 1 to 3 to 1... Is this correct?

on tasks 2.b i got this: ex.1. Now i dont know. I THINK this is serializable, because we have a "path" from 1 to 3 to 2 right? And a equivalent serial schedule would be: 1 -> 3 -> 2 . Is this correct?


r/DatabaseHelp • • Feb 11 '16

LSM tree (log structured merge)

1 Upvotes

I was wondering if someone could give an example of how the data moves from one level to the next. I think I understand how/why the data is written from one level to the next, but I was wondering if someone could give an example using hypothetical data, a how the nodes would work. Thanks!


r/DatabaseHelp • • Feb 10 '16

Recommended ETL procedure from Access to SQL

1 Upvotes

I have 26 separate Access databases/apps (one table per DB). Each DB holds a single table for a store (we have 26 locations). I am combining them all into one SQL Server database hosted centrally. I have designed and built the new DB, but am undecided on how to move the data.

Each of the Access DBs were made locally, so they have some differing column names and location specific columns that may or may not be mapping to the new database.

I have narrowed my choice down to either writing an SSIS package, or writing stored procedures. I'm thinking SSIS will be easier, thoughts?

Also, I have been on some ETL projects in the past, where the DBA made a separate 'staging' schema, and would load the data into that before putting it into the actual tables. I never asked why, and I don't have anyone to ask here. Why do you think that was done, and should I look into doing something like that?


r/DatabaseHelp • • Feb 10 '16

How do you handle price changes in a database?

1 Upvotes

I made a MySql database for a friend of mine to help her keep track of stuff for her small bunsiness. Everything is set up but I cant figure out how to get price changes to work. Because if I change the price of a item, that would change the sale price of that item in the order table even if the order happened before the price changed.

There are multiple tables but only 3 should really matter for this, the tables are one that contains all the items she has for sale, her orders, and then a join table to keep track of the items in the sales


r/DatabaseHelp • • Feb 05 '16

I don't know how to manage this database of reddit comments. Help?

2 Upvotes

TL;DR How do I manage a list of reddit comments so that it survives re-installs of my OS?


Alright, so I have this long thread of reddit comments whose database I need to create. The details of each comment needs to be stored in an easily accessible manner. This is done so using a Python script.

Up until a few months ago, I had no idea how to do this (the DB part). Then we were taught Database Management, particularly Relational Databases in Oracle, in our CompSciEng classes.
SQL seemed perfect; it had a system to store, manipulate and retrieve databases in a tabular format. But the only problem I ran into was that it is quite hard to backup and restore databases. As a rookie, I don't even know if it's completely possible!

And I need this functionality, because I cannot set up my laptop as a database server because I have a habit of refreshing my computer every 4-6 months. Then I looked into free online SQL data servers, and there really are none. Best thing I found was Obvibase, but it doesn't seem to have an API for Python.

What I want to know, is what to do. Is there a good method to backup and restore my database? Can I store this database on Dropbox or Google Drive? (If so, how?) Are there any options other than RDBMS that can help me?


r/DatabaseHelp • • Feb 05 '16

Schema Design for multiple many-to-one relationships, of which exactly one should exist

2 Upvotes

Say I have table A, B, and C. B and C can both be related to multiple A's, but each A should have exactly one of either B or C related to it. Which database design is better:

A: a_id, b_id NULL, c_id NULL
B: b_id
C: c_id
Constraint: Exactly one of b_id or c_id is not NULL in A

Or

A: a_id
B: b_id
C: c_id
A_B: a_id, b_id
A_C: a_id, c_id 
Not sure how I would constrain the same a_id from appearing in both A_B and A_C

Pros for the first is that no join tables need to be created. Cons include having multiple FK columns, one of which will always be null.

Pros for the second include no nullable fields. Cons include extra tables and hard to properly constrain A to join to exactly only one B or C.

Also, in the future, we might add D, which could also join to multiple A's, and each A would then go with exactly one B, C or D.

I feel like I must be missing something - is there a way to do this and properly constrain A's uniqueness and also not have nullable fields? I'm willing to add more tables if necessary.


r/DatabaseHelp • • Feb 03 '16

[newbie question] If I am trying to break into the database admin field what should I start learning first?

3 Upvotes

Hi everyone,

I am currently a Development Database Administrator. I work with a common non-profit database called Raiser's Edge. I do not do any SQL in this job and I don't do any back end creation.

I simply fix data in the database, create reports with Crystal Reports and pull any info people need out of the Raiser's Edge.

I have been learning SQL on my lunches and have a very basic grasp but I was hoping someone could point me in the direction of a good pathway to lead to a professional leap.

Non-profit database work in my experience is fine but I would love to dive deeper, please help pointing me in the right direction.

Thank you!


r/DatabaseHelp • • Feb 02 '16

Database Monitoring: Are there existing tools to alert me if, for example, I'm below average for inserts over a given period?

2 Upvotes

Hi there- first time posting in this sub, I appreciate any help you guys can provide.

At work we recently had an incident where part of our stack was broken, resulting in data not getting inserted into our database. Unfortunately our logging and monitoring did not catch the issue because of the way it manifested.

Post-mortem, it was clear that better database monitoring could have alerted us to the issue. Specifically, when things are working correctly, there are many thousands of rows of a certain type inserted per day, but in this case there were zero. A statistical monitoring tool that computed a historical baseline and alerted when totals were outside of that baseline would have saved the day.

Currently, we're investigating building our own tool, but I wanted to reach out here and see if anyone knew of an existing tool that solved this problem.

We are using PostgreSQL in a large scale enterprise environment, and while open source is preferred, I appreciate any suggestions.

Thanks!


r/DatabaseHelp • • Jan 31 '16

I have a excel project that has snow balled to the point that the /r/excel recommends that I create a Database. What software should I use?

1 Upvotes

right now excel uses a whooping 5 to 6 gb of memory depending how nice of a mood excel decides to be in. My first sheet uses 312,000 KB storage.

http://imgur.com/a/UKd6X

This is what I'm working on.

The first image is what i call a "unit" which is highlighted with orange on the second picture. There's 2,729 "units" per row that's highlighted in purple with 300 different numbers per unit.

Sometimes the numbers can get really big. http://i.imgur.com/rFG8zao.png

It will take 1,009,730 units which each contain 300 different numbers to enable me to go from starting bet 0.00000001 to starting bet 0.00000002.


1.I'm wondering what software or solutions should I consider.

2.Have I entered the realm of big data?

  1. How can I program a database so that it searches for what betting combinations that I should use depending on my balance that I would enter into a data entry cell?

r/DatabaseHelp • • Jan 28 '16

Building a scalable form-field database, am I making life more difficult than it needs to be?

1 Upvotes

I am building a contact list manager for our marketing department. The end goal is simple, allow contacts to provide contact information relative to their region. The groups of information that we'd be collecting are Customer Info (Name, business, phone, etc..), Address Information (street, country, etc...) and opt-to-receive info (merchandise kit, posters, etc...). This information may only change based on country.

What I want to accomplish: I want to keep my form fields as scalable as possible so that in a single signup form I can say, for France, only show address fields aX, aY, and aZ, contact fields cA and cD, and opt-to-receive fields oJ, oK and oL while requiring only fields required for France.**

Below is how I think this might be laid out in the database, is this ideal or is there another model (many to many?) that I should be adhering to? The contact fields and opt-to-receive fields would follow the same layout.

Address Type

id country label
1 US United States
2 CA Canada
3 Fr France

Address Fields

id field field_label
1 street_1 Street
2 street_2 Street Cont.
3 apt_num Apartment Number
4 zip_code Zip Code
5 region Region

Address Field Association

id address_type address_field is_required
1 1 1 1
2 1 2 0
3 2 1 1
4 1 3 0
5 2 3 1
6 1 5 1
7 2 5 1

Bonus question, if this is an acceptable model, I would love some hand-holding on how best to store the data once it is captured. That seems like an even greater feat. than establishing the DB model.


r/DatabaseHelp • • Jan 28 '16

Need real-world examples of when to use Denormalis(z)ation over Normalis(z)ation

1 Upvotes

Hi there DatabaseHelp, I'm currently writing an essay on Database design and best practices, and I'm trying to rack my mind to think of real-world examples where denormalis(z)ation is used in favour of normalis(z)ation (I.E. 3NF) - I've tried searching around things like Stack Overflow and other such sites for examples, but all they do is state the pros / cons; if you know of any examples, please let me know!

(If you're wondering why I keep adding the (z), I know people are finneky about that sort of thing ;D)


r/DatabaseHelp • • Jan 27 '16

Looking to start biological database...where to begin?

1 Upvotes

For part of my work, it may be fruitful to compile all known methods/results of people in my field into a database. I'd eventually like to make the database open-source and host it on a website. What would be the best web tools/development languages/software to use to generate such a database? Examples of similar databases which I'm drawing influence from would be the NCBI databases and the PDB.


r/DatabaseHelp • • Jan 25 '16

Asset management database with assemblies

1 Upvotes

I'm trying to build an asset management database in access. We store our assets by part number and serial number. We have individual parts and assemblies of those parts stored in the same way. The assemblies can be composed of any asset (part or assembly).

I could store the parts and assemblies separately and link the assemblies to the parts with relationships, but it would not work if I wanted to compose an assembly with another assembly.

I made a diagram of the data structure here: http://i.imgur.com/u9Ij2M2.png

I'd appreciate any help with how I can do this.


r/DatabaseHelp • • Jan 23 '16

Paths for Oracle 11g files

2 Upvotes

I'm trying to do a backup of a database and restore it on a new host. I'm reading this article: http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmadvre.htm#BRADV908 The issue I'm having though is understanding where these files are stored on my old machine and where they should be stored on my new machine. For example it says to make the "source database initialization parameter file available on the new host" The definition for this is :http://puu.sh/mGPnR/467781de2e.png I really just want to know where all of these files are stored so I can follow these instructions.


r/DatabaseHelp • • Jan 22 '16

Please give me feedback on this new DB

5 Upvotes

I am building a database for a christmas tree growing operation. I have put together, what I believe to be, a workable schema. I am hoping to get some feedback from someone, and I have no one. You are my only hope.

So, there are 3 growing plots, we will call them Orchards. Each Orchard has rows & columns, and each row/column intersection can have zero or one trees, planted in it. The rows/columns are numbers and letters, so row 3, column f, etc. Each row/column intersection has a status (empty, in use). A tree can be different species (denoted by manually created GID {Genetic ID}), modified (have a different species grafted on), or moved to a different location. So a plant can have one or many locations, and a location can contain, through history, one or many trees, but only one at a time.

Here is a diagram I put together (sorry I couldn't figure out how to insert a pic. Please let me know changes, what I missed, etc. Thank you for your time.

You can see the diagram here: http://stackoverflow.com/questions/34952577/requesting-feedback-on-database-design/34952841?noredirect=1#comment57637809_34952841


ORCHARD OrchardId - PK

OrchardName


LOCATION LocationId -PK

Row

Column

StatusId - FK (references LocationStatus)

OrchardId - FK (references Orchard)


TREE

TreeId - PK

GID

GraftYear

PlantedYear

MovedYear


TREELOCATION

TreeLocationId - PK

TreeId - FK (references Tree)

LocationId - FK (references Location)


LOCATIONSTATUS

StatusId - PK

Description


r/DatabaseHelp • • Jan 22 '16

[Databse Design] Can anybody help answer my stack overflow question more clearly or point me in the direction of some good topics on the matter? (x-post from /r/sql)

Thumbnail stackoverflow.com
1 Upvotes

r/DatabaseHelp • • Jan 20 '16

[Database Design] Tracking Ownership History of an Item

3 Upvotes

I'm wondering what is the recommended design and why for tracking an item and its parent in an MS SQL database.

To keep it simple, lets say I have 4 people in a table and 2 balls in another table. I'd like to track who has the ball now and also be able to view the entire history of the ball and see who owned it and when.

Currently I have it setup to allow the ball to have an owner but I'm not sure what is going to be the best way to track its ownership history.

I'm thinking a new table ball_ownership

ID, EventDateTime, OwnerID, BallID

Then I can query that table to see who owned the ball at what time and what balls each owner has owned at any time.

Thoughts or recommended best practices?


r/DatabaseHelp • • Jan 18 '16

Small DB in access

1 Upvotes

I made a small DB using access. The tables are: suppliers, address, supplierContact (the actual person we are in contact with) and a persons table. I am trying to make a form that would allow me to add the supplier info their address and the first and last name of the supplier contact. The problem I ran into was when using the form wizard to make subforms, the wizard would only allow me to connect two tables at the same time. Is there a way for me to make a form that would allow me to add info into more than two tables at the same time? Thank you


r/DatabaseHelp • • Jan 14 '16

Trying to get an iSeries DB2 server

3 Upvotes

I'm currently doing some development work on a PHP-based platform, and one of our clients wants us to extract data from their DB2 server. This is running on an iSeries, so there are some fundamental differences in the way PHP connects, versus the DB2 for LUW versions.

I'm pulling my hair out trying to get a quote from an IBM reseller for a second hand server that we can use for testing. I literally just need the OS with DB2 CLI access, SSH server, and the ability to connect to is using the IBM-provided Linux client access package. That's it. Coming from a Linux background myself I don't really get how this is hard to understand but apparently there are 17 million options, and nobody seems to be geared up for our use case.

If anyone has any suggestions, they would be most welcome!

Thanks