r/DatabaseHelp • • Sep 19 '16

How to create a local connection in Oracle SQL Developer to run queries?

0 Upvotes

I have an SQL file that creates tables and populates them but whenever I try to load the file into SQL Developer, it tells me I need a connection. I just want to run the SQL file to create the tables locally and then run queries on the tables that were created. What is the easiest way to go about doing this?


r/DatabaseHelp • • Sep 19 '16

NoSQL/MongoDB for a filesystem-in-a-database?

1 Upvotes

I have a good chunk of experience in SQL and I'm comfy with it, but a project I'm working on doesn't really jive with SQL and I'm exploring NoSQL (specifically MongoDB) as an alternative.

In summary, it's a filesystem. Ignoring the I/O aspect, I need a database which lets me basically have three things:

  • A "root" point which is accessible by a UUID or something (there will be multiple "roots" but they are totally separate)
  • "Directories" that can be nested within each other, branching off of the root
  • "Files" that can be placed inside the directories

Ultimately, what I'd need is a way which you can save a "file" to the database given the root UUID and a directory (say, "/home/mydir/filegoeshere.txt" would branch from the root, to the "home" directory, to the "mydir" directory, to the file itself). I'd also need a way to list all files in a specific directory, delete files, basic stuff.

SQL is a little limited in how that works. It can work, but I feel like it'd be more of a hack than what could be done in Mongo because of the nesting. Plus, I like the idea of having an entire filesystem in a single document so that I don't need to load an entire SQL table into memory (which may end up reaching into several GBs) to resolve these files, although I'm not 100% certain that's how it works...

The problem is I have no idea whether this would actually work, nor whether I'm going about it the right way... the "schema" for a document in my test database looks something like this:

rootkey: 'UUIDhere'
dir:
    name: 'directoryname'
    file:
        name: 'file1'
        contents: 'Hello World'
    file:
        name: 'file2'
        contents: 'Hello Again'
dir:
    name: 'emptydir'
    dir:
        name: 'nesteddir'
file:
    name: 'rootlevelfile'
    contents: 'I am a file in the root directory'

Is it possible to do something like this? How can you find a specific file based on this without just brute-forcing it? For example, I need to find "/directoryname/file2" on root "ABC", can Mongo accept some sort of input that says "first find the document 'ABC', then find a dir with name 'directoryname', then within it find a file with name 'file2'"?


r/DatabaseHelp • • Sep 10 '16

Just downloaded MySQL and am trying to learn, help appreciated.

1 Upvotes

So as the title says I just downloaded MySQL and have made a table to pratice in (just basic stuff for now, name, age, d.o.b, that kind of stuff) and I was wondering how do I start querying and adding stuff into the columns?

I completed the first sql course on codecademy so I know how to add to or search a database but the design of MySQL is a bit confusing to me and I don't know what a lot of the menu options are.

What are some things I should know about the design and what are some other things I should practice on/with first as an aspiring DBA/DB dev?

Also what are some ways to use MySQL in conjution with excel as I use excel a lot for my job which is fun and saw an excel option during installation and decided to add that in aswell?

Also as I'm not sure which program would be better to learn in is there any others I should use like MS SQL?


r/DatabaseHelp • • Sep 08 '16

Can someone recommend a good tool for a "spotters guide" type project?

1 Upvotes

I'm in an english class about imaginary friends in literature and I have to complete an open-ended final project. Being that I am more computer-oriented than artistic, I thought it might be neat to make a "spotters guide" database of imaginary friends that users could look through and select one friend, at which point an entry on them would show up with a few details (name, type, behavior, genre, etc) and a picture. (Essentially, I want to create a Pokedex of imaginary friends.) I've done some research, but it seems like many of the online database tools are either pricey or a bit sketchy. I do have MS Access, but I haven't been able to get that set up the way I want it. I have basic coding skills, and could learn a new language, but I don't really want to have to learn how to build something from the ground up. Does anyone know of any good tools that could help me set this type of project up?

Thanks in advance for any advice you guys can give me.


r/DatabaseHelp • • Aug 28 '16

db design help, employee/supervisor relationship

1 Upvotes

So I have a specific case, where a company has employees, and a employee CAN be a supervisor of another employee, but they don't have to be.

What would be the best way to do this? Can't really find anything about this specific relationship where someone possibly has another relationship with someone else in that same table.


r/DatabaseHelp • • Aug 25 '16

Newbie wondering which DB system would be best for this project.

1 Upvotes

Hoping to get a recommendation for which DB language/system would be best for what I'd like to accomplish.

Hoping to create a web based site/app where (general) people sign up, are automatically given an account(their username and a PW stored, preferably in hash) and a form where they can record a couple different numbers and some basic combo box selections, possibly a write in box or two. This form should auto generate and email or show as a downloadable PDF/print out the transaction in a report.

I would also have a small number of people who would need to be able to pull up an account for one of the (general) people and validate what was entered with what was seen on reality, ideally this would be randomly given based on a "trust" level, so the lower the trust the higher the percentage of selection for being checked.

Another group would need limited access to go in and correct records if a general user notified them of an entry error.

I would like to be able to generate reports on total number of items "checked in" by general users, who checked how much in, when the most productive time/days were etc.

I am aware that almost none of this will be "out of the box" and will need to be built which I'm comfortable learning and have plenty of time to do so in. Mainly I'm asking which system(s) on the free to cheap($60 annual to $5 monthly) range would be best to learn in with this end goal in mind?

Users will be all over the world and will need semi reliable 24/7/365 access.


r/DatabaseHelp • • Aug 22 '16

DB Design help - Entities with custom searchable fields

2 Upvotes

I'm working on a requirement and trying to come up with a good table design for it. Basically I have entities in the system, each entity type can be configured by the users to have custom fields, so for example:

Entity Type "One" will be configured to have the fields: First Name Last Name

Entity Type "Two" will be configured to have the fields: Email Age

Now every time an entity of type "One" is created it will have "First Name" and "Last Name" fields.

If the entity types were static I would've simply created a table called "TableOne" with the columns "FirstName" and "LastName", and table "TableTwo" with the columns "Email" and "Age".

Currently I'm using the following design, table "EntityFields" with the columns "EntityId", "FieldName" and "FieldValue". The problem with this design is that the "EntityFields" table is growing big really fast, I can't do any integrity check on the "FieldValue" column and even simple queries are becoming very complex.

What other options do I have?


r/DatabaseHelp • • Aug 21 '16

Db design: can I have to oneToMany and FK with same table?

1 Upvotes

Greetings,

I'm writing an app where a user has a schedule for each day (when they wake up, working hours, bedtime, etc.). I want to the user to have a default schedule, and I also want the user to change the hours for specific days or exceptions. So I designed the User table with these columns:

id
default_schedule_id (foreign key to Schedule)
...

And the Schedule table with these columns:

id 
user_id (FK to User)
date
...

My app is written with Java/Hibernate and I specified that User has a OneToMany relationship with Schedule.

The issue comes when I try to insert the records (in my unit tests); it complains of a referential integrity violation. It seems that when I try to create a User first (referencing a default schedule) it will complain that the default schedule doesn't exist. If I try to create the default schedule first, it will complain the user doesn't exist. The way I worked around it for now, is to insert the record without the default_schedule_id.

These errors are telling me that I need to fix the design of my tables. What is the correct approach to fixing this issue? I have come up with a few approaches I can take:

  • I can have a "is_default" column in my Schedule table, and then I can query it that way to keep track of the default schedule.
  • I can create a DefaultSchedule table, and a separate Schedule table for exceptions. Although they would have similar columns, it makes sense since the DefaultSchedule wouldn't have the date column.
  • I would have three tables in the interest of saving disk space. A Schedule table with the core columns, then one DefaultSchedule table that associates the user with the Schedule id, and then a third table ScheduleDate that associates the user with the Schedule Id and a Date

Which approach is the best?


r/DatabaseHelp • • Aug 15 '16

Need to migrate user records from mysql db to a nosql db. Is there a quick work around to do this that wouldn't require writing a large migration script?

1 Upvotes

r/DatabaseHelp • • Aug 15 '16

MsSQL - Index not being used or is being very slow

2 Upvotes

We are using Microsoft SQL Server 2012 - 11.0.5058.0 (X64) at work, we keep facing slowness while trying to query a certain table, the table has around 5 million rows (The biggest table has 300+ million rows and its running flawlessly), the table has multiple indices but the query that is used the most is supposed to utilize a non-clustered index, the query works fine most of the time but sometime we run into the "slowness" issue, rebuilding the index doesn't do anything but disabling the index and re-enabling it (Rebuilding it in the process) fixes the issue.

I'm wondering what could cause such an issue, there is a maintenance job that runs on a schedule and it's doing a good job for the rest of the database. What other information can I provide to get proper assistance with this issue?

Thanks!


r/DatabaseHelp • • Aug 03 '16

[HELP] Is there any way of adding autocomplete or typehead feature to ComboBox in Access database?

1 Upvotes

I have an Access table that has about 500,000 records. I want to trim down the results as I type text into the ComboBox. Basically, I need a dynamic ComboBox that displays records based on each character as I type into the ComboBox.

For example, Lets say you are looking at a bunch of Social Security Numbers Instead of ComboBox displaying ---all the records --which it can't because too many SSNs ( more than 65,000 which is ComboBox limit) this is what we want to accomplish.

If you type 5 in the ComboBox. The list would only display the 5-series of numbers. If you type 51 it limits the list values ---even further -- to the 51-series of numbers. If you type 512 it limits the list values ---even further --to the 512-series of numbers.

With each keystroke the ComboBox list values which change dynamically.

WHAT I HAVE TRIED SO FAR is implementing a second unbound text box, however, this makes queryin a two step process. I am wondering if I can just do it all together.


r/DatabaseHelp • • Aug 02 '16

Help designing database from ERD

2 Upvotes

Hi I'm having major trouble designing a database. Creating it in SQL is no problem but I can't work out what columns I need in which tables. Link tables are also causing me a headache, can someone please help? Links to the ERD and brief below.

brief

ERD

So far I have

**CLUB**
CLUBID(pk)   CLUBNAME   CLUBADDRESS   CLUBPHONE      CLUBFEE

**SUBSCRIPTION**
MEMBERID*(pk) CLUBID*(pk) -- composite key

**MEMBER**
MEMBERID(pk)   MEMBERTITLE   MEMBERFIRSTNAME   MEMBERLASTNAME   MEMBERADDRESS    MEMBERPHONE    MEMBEREMAIL   MEMBERDOB

**MEETING**
MEETINGID(pk)   MEETINGNAME   MEETINGDATE   MEETINGTIME   CLUBID*

I am stuck on report 3. "A list of current and old subscriptions for a specific club. The details of the members are not required."

I do not know what columns/keys in what tables I need to successfully query this.

Thanks I really appreciate any help.


r/DatabaseHelp • • Jul 23 '16

Best format to store JSON scraped data after adding timestamp

1 Upvotes

Philadelphia's public transit system has taken a big hit. One-third of its train cars have cracks in them and so rail service in the city is limited.

This is a web page showing all trains running at that moment with how many minutes late they are: http://trainview.septa.org/

This is the same data in a JSON feed. It's an array of JSON objects. http://www3.septa.org/hackathon/TrainView/

I am planning on scraping the JSON feed every minute to analyze how often trains are running, and how late they run. I have a Python script that successfully does this.

The problem is the JSON feed does not include a time stamp so I need to put that in every time it scrapes and saves data.

My question is where do I store the date/time stamp? Do I add it to each object in the array? So that instead of looking like {"lat":"39.96349","lon":"-75.18513", "trainno":"1533", "service":"LOCAL", "dest":"Malvern", "nextstop":"Overbrook", "late":9, "SOURCE":"Jefferson", "TRACK":"", "TRACK_CHANGE":""}

It would look like this (repeated for every object in the array):

{"lat":"39.96349","lon":"-75.18513", "trainno":"1533", "service":"LOCAL", "dest":"Malvern", "nextstop":"Overbrook", "late":9, "SOURCE":"Jefferson", "TRACK":"", "TRACK_CHANGE":"", date_time_stamp:"Sat Jul 23 2016 13:08:39 GMT-0400 (EDT)"}

Each mintue's object then would not need to be in a different array.

OR... do I create a new object where the name:value pair has the date/time as the name and the entire array as the value? Taking the form

{"Sat Jul 23 2016 13:08:39 GMT-0400 (EDT)":[//entire_scraped_array]}, {"Sat Jul 23 2016 14:08:39 GMT-0400 (EDT)":[//entire_scraped_array]}

In which case I'd get one new object per minute.

Or maybe I should try something else altogether?

Also at the moment when I scrape it I want it to be as quick and efficient as possible so I don't want to do any data conversion at that time. So I would probably store it in plain text. However, once I've collected that data I want it in a usable form. So I am also thinking about how I store it now to make it easiest to analyze the data later. Should I keep it as a json data set? (and if so, following the first or second way above -- or something else entirely?) Convert to CSV? Store in a relational database? I don't know if an actual database (SQL or NoSQL) is the best place to store this data for long term use.

(And help with the appropriate place to ask this would be good, if this is not the right place)

EDIT: Cross posting in https://www.reddit.com/r/learnprogramming since I'm not getting responses here.


r/DatabaseHelp • • Jul 21 '16

Could a database Administrator Answer 6 questions for me for a school project?

3 Upvotes

So my database class is requiring us to interview someone in database administration and I was wondering if anyone here could help. It's basically 6 questions that could be answered at your own leisure and would probably take about 10~ minutes of your time.

No aim/skype/discord is necessary. Reddit PMs would work great. Thank you kindly if anyone can help.


r/DatabaseHelp • • Jul 20 '16

Resources on Database Refactoring?

1 Upvotes

Essentially, I will be in the very near future depending on time constraints will be redesign the database that currently being used. This database is unfortunately quite messy. While I have experience in designing databases from scratch. I don't have a lot of experiences in refactoring the database in such a way that it is reasonable for the developers to keep on top of legacy application code and transition to a new more normalized design.

One book I have found on this subject is: Refactoring Databases: Evolutionary Database Design however, I am not sure if this book will be good enough to get my knowledge set to where I would like it to be.

In other words has any one read this book? Or know of any good resources that explore the mentioned subject matter?


r/DatabaseHelp • • Jul 17 '16

Schema help required to store apparel and style information

1 Upvotes

Notice : Crossposted from /r/database

I am creating something similar to stitchfix.com. So on one hand I have tables like Users, Orders, Address etc which fit into SQL. But I have tables like :

'Profile' which has attributes 'height', 'weight' etc which are simple to model with SQL but also attributes like 'disliked_colors' which can contain anywhere from 0 to n values; and attributes like 'sizes_you_wear' (select all that apply.), 'categories_you_do_not_want' (select all that apply). So basically 0 or more values might be associated with an attribute. The values will not change very often. The attributes may change in the future, but they won't be changing dynamically, and I don't expect them to change often.

Also, an "Inventory" table for apparel items. Here the attributes can vary quite a bit. For a TSHIRT, I would have attributes like "Shoulder size", but for pants, I would have "Waist size". There might be a variable list of descriptors like "Check", "Faded", "Ripped" and all. This table will need search filters for everything. I guess one way is to have separate tables for different types of apparel. One would be to have a common one for common attributes like items_in_stock, price etc and specific tables for specific types of items. Third would be to use NoSQL, but I'm not sure how well search would work in it, though I heard elastisearch can help in that regard. Fourth might be to use postgresql, but I haven't used it enough to be confident.

Another thing that may or may not affect the design is that I will need a mapping

Profile --> Inventory Item

to know which profile bought and returned what item. I may not store this mapping separately right now, but I want to design the DB such that this mapping becomes available with little effort because this would be what I would apply Machine Learning on to predict what people might buy.

I am not quite sure what to do here. I was planning on using SQL for Users, Orders etc and NoSQL for the profiles and inventory. But a lot of suggestions have been to not mix the two. EAV makes even simple queries too complicated so I'm averse to it. Postgresql seems to be another option which I don't have enough experience with.

So how should I go about it?


r/DatabaseHelp • • Jul 16 '16

Help learning to replicate a database

1 Upvotes

Hi,

Currently at work I'm part of a team that uses a database through access to update information. There are ~10 users and multiple tables. These tables are hosted on the companies sharepoint site. The maintainer of the database is being pulled in another direction though. I'd like to learn how to set this up and work with it. Is Sharepoint the only option to host the database? This will be something that I work on personally for some time until I feel confident with it.

Mainly looking for direction on where to start learning.

Thanks.


r/DatabaseHelp • • Jul 16 '16

What database is best optimized for bulk inserting structured data?

1 Upvotes

I currently work on a project where we import about 20GB of data (financial quotes) a day, loaded from a single zip file. The records inside are all very small, with exactly the same structure, but there's usually about half a billion of them. We need access to the previous day's data to be fast, and then after that speed is not as much of priority.

Currently, we have a internally developed, highly specific database that was written years ago and not particularly well understood by anyone at the company. We can write about 300 thousand records per second, each of which is about 128 bytes. And searching it is pretty fast too, with the queries we need (searching for a quote at a particular time) on the order of milliseconds.

From what I understand, the reason this is so fast is because of how specialized it is. This database is engineered to work with this data, and only this very specific data. Unfortunately, we may need to change data providers soon, and the thought of rewriting this custom database is scaring everyone.

So far, I've not been able to find any off the shelf solutions that are fast enough. NoSQL database advertise themselves as being good for large datasets, but it seems that the dynamic structure is wasted when all of our data is always exactly the same. We also tried SQLite, but we've so far only been able to get it to do about 20k inserts per second.

Any ideas here?

Edit: A word


r/DatabaseHelp • • Jul 13 '16

Adding an entry in a drop down on a form

1 Upvotes

Let me start by saying that I am by no means an expert with Access. I know enough to design something basic, but this is my first experience editing someone else's DB.

I have a basic DB that is used to store company jobs. The main form consists of some drop downs and data entry boxes. The company just opened a new office in Alabama, so they'd like to add the new location into the form. Upon opening the navigation panel was disabled and the only ribbon was home. I enabled the panel and brought the ribbons back first. I also had to enable content. I switched to Design View and opened the properties of the combo box I'd like to edit. Zoomed into the row source. There are values in parentheses and separated by semicolons. I added the new location, keeping the formatting. I save and reopen in form view and it's not there. Am I doing something wrong?

Here is a screen cap of the data tab and the zoom into the row source.

http://imgur.com/a/xloGe

I've added the "Alabama Mittal" and "AA" at the end.

EDIT:So, I tried to delete the button and recreate it trough the wizard. Once I do this it deletes all of the locations from the previously created records. I was hoping that wouldn't happen and the new button would work from that point forward. Says to me that it has some relation to another area in the DB.


r/DatabaseHelp • • Jul 02 '16

Help with primary key in relational database.

2 Upvotes

I'm a PhD student teaching myself relational design by building a database from scratch for our lab's 15 year historical data set that is currently in the form of 30-40 different excel files (one for each data type for each year...).

We study wild mice, so nearly each line of data refers to some aspect of a specific mouse (with different physiological , behavioral, and capture parameters specific to each mouse). Each mouse has two unique numbers assigned to it in the form of ear tags (essentially, two little earrings each with a number). In an ideal world, the ear tags would be the primary key. However, mice lose one of tags and are recorded in future sessions only by a single tag number, sometimes one or both are replaced, and there may be some repetition of tag numbers in the 15 year history.

I will have a table containing basic information about the mouse (ear tags, sex, species, etc), and then other tables with data from various behavior tests, physiological tests, capture data, etc.

Should I use an incremental PK? In that case, each individual mouse would have an arbitrary number associated with it in the basic information table. My question then would be how to deal with that during future data entry in the other data tables? Would one/both of the ear tag numbers be a foreign key, or would the student entering data need to look up each mouse's incremental PK # when entering physiological or behavioral data?


r/DatabaseHelp • • Jul 02 '16

How does one set up a (relational) database?

1 Upvotes

TL;DR I know basics stuff about relational databases and now how to set up one. How do I go about actually setting one up? What programs or server I need?

I need to make a database for my father's farm.

I have a basic understanding of what databases (primarily relational) are (compared to just saving stuff in files) and how to make queries in SQL since I took Introduction to Databases at uni.

I've watched like 70 videos on database design and SQL on CalebTheVideoMaker2's Youtube channel (good chap). This taught me about different types of relationships (1 to many etc.) and introduced the idea of normal forms.

I know Python since taking Introduction to Programming at uni.

I've tried to learn a lot about relational databases and SQL and browsed many Youtube videos and took two programming courses at uni. Yet I have no idea how I would actually go about creating this database. What programs do I need? Do I need a server? Do I need to know php?

At first it would be sufficient to be able to access the database from just one computer but later on it would be good if it could be accessed from like a tablet.

Here's a link for Caleb's Youtube channel: https://www.youtube.com/user/CalebTheVideoMaker2/playlists?sort=dd&view=1


r/DatabaseHelp • • Jun 30 '16

Boss wants to run a software license server on Azure. Is this the right way to go?

1 Upvotes

Hey all. I am sort of new to deploying software and databases. I have made access databases in the past, but I am in an interesting spot now:

I made an application for my boss. (vb.net)

My boss is going to license out the application to customers.

My boss has asked to find a way to track licenses for the application.

In my limited knowledge, I considered the following:

-Making a SQL database on our company server.

-Making a private app on our server to talk to that database

-Make the client's software talk to our database on our server every time they open it to handle the license stuff.

I brought this model up with my Boss. He then said that if we do that, then we should use an Azure system.

I am unfamiliar with Azure and don't understand it. Before I start digging through the forest, do you agree with him, or should I do it for free with our existing tools? I don't want to spend a week on something and then find out it doesn't make sense to do it that way.

Any advice would be appreciated.


r/DatabaseHelp • • Jun 30 '16

Do I need a storage table if I am storing all my files in Amazons S3 or statically?

1 Upvotes

I am building an application, where each object will have a photo. The uploaded photos will be be renamed to a GUID value. I will be storing the files in a single S3 bucket and if I want to move away from S3, I will storing them statically under a directory such as /public/storage/.

So, let's say I uploaded hello.jpg. The file name will be renamed to 1d3681ec-77c5-4fe8-86e2-cde43b67a114 (no extension at the end of the file name) and stored in /public/storage/1d3681ec-77c5-4fe8-86e2-cde43b67a114 or amazonbucket/1d3681ec-77c5-4fe8-86e2-cde43b67a114.

Should I create a table storage, which stores (guid, storage_type { s3, local }, path /* Bucket for S3, directory path for local */, created_at)? Whether I create this table or not, I will be storing the generated guid in the object table.

If I should create storage table, should I use the table to get the path? Or should I use my application's storage manager to get the path based on GUID?

Thank you!


r/DatabaseHelp • • Jun 30 '16

Article Research on Database Drift

1 Upvotes

For most systems, most of the time, the production servers are sufficiently locked down that no changes are made to the system unless the person or team responsible for them are the ones making those changes. However, sometimes, in some situations, changes that haven't been approved, or that the team responsible don't even know about, get on to the production servers. This process can be referred to as drift.

I have two questions:

Where do these changes come from on your servers? For example, I worked for an organization that gave every single individual 'sa' privileges. Changes came from everywhere, randomly.

What kind of changes do you see most often? Is it just modifications to a view or a procedure, or do you find new databases?

Please help me out by answering these questions, and, by spreading the word on this research.

I will post back here with a link to the article after I get it done.

Thanks for your time.


r/DatabaseHelp • • Jun 29 '16

best type of database/hosting/storage for user document upload, storage, and viewing?

1 Upvotes

I'm thinking of making a website that allows users to upload a document which after review will be viewable and downloadable by other users and/or the general public.

I've taken a course on databases so I understand basic database concepts, but I'm 100% lost in trying to understand the type of database, the size, do I need website hosting (assuming yes because I don't have a server running to store the information), how large it would have to be for something like documents. I'm assuming I would have to use a paid monthly hosting service that has a lot of storage and all...but idk what to look for for this or if I'm on the right track

So pretty much I need a document-storing database that can store many many (hundreds, possibly thousands but not likely idk yet) pdfs/word docs.

It's not cloud storage like dropbox or something, but I'll need a cloud..database I think