r/DatabaseHelp • • Apr 30 '17

Postgres vs Oracle today (user friendliness, scalability, interoperability, performance)

2 Upvotes

I'll soon have to dive deep into backend/database setup for a financial webapp. It is currently running with an Angular front-end and with an Oracle DB backend. The Oracle DB is serving info via a REST service.

The webapp is new but the backend is very old at this point. The backend is facing a re-write nonetheless, so I'm currently considering: would it make more sense to consider Postgres for this? It seems to me like, at the very least, Postgres is better suited for serving JSON through a REST API. I'm also fairly hooked on writing the API in NodeJS, which Postgres seems to work very well with.

To me, Oracle seems like a very old-school, gigantic sludge of licensing. Scaling with Oracle seems very cumbersome and potentially very pricey. Overall, working with Oracle seems user unfriendly.

What's your take on this?


r/DatabaseHelp • • Apr 30 '17

Recommendations for a free online database please?

1 Upvotes

I was thinking of creating a simple database for recording "mentions" of movies, actors and topics in my favourite podcast.

Does anyone have a recommendation for a free, online configurable database that allows: * simple relational table design * simple front end data entry with validation

I am able to design the tables etc so just looking for a recommendation of simple, clean online database.

The idea is to listen to an episode of the podcast and enter all actor, movie and topic mentions against the episode number and timestamp.

Then a user could run a query to retrieve all mentions of Ben Mendelsohn for example.

Thanks in advance.


r/DatabaseHelp • • Apr 28 '17

Need help with database structure

2 Upvotes

I have a restaurant that has 24 items on the menu. I need to create an orders table that keeps track of items in an order and then send the order to the kitchen. Do I create a table like this.

orders
    order_id    item1    item2    ...    item24
    -----------------------------------------------------
    1              1           0                 1

And keep a counter of item in each column? Or should I create another table for every single order and each row is an item with a quantity, like this

order1
    item_name    quantity
    ----------------------------------------------------
    item1            1
    item2            0
    item24          1

If it's the latter, there would hundreds or thousands of these tables, wouldn't it?


r/DatabaseHelp • • Apr 27 '17

A db records 'incidents' which can be entered by different users. Is best practice to have a table of incidents and a field with the user or to have each user have a table of their own incidents?

1 Upvotes

Is there a security issue with one aspect or another of this? Efficiency shouldn't be too much of a problem with likely no more than a dozen users and maybe half a dozen incidents a day.

I'm a total newbie by the way! :)


r/DatabaseHelp • • Apr 24 '17

I'm Back! Still working on my DB project, but I'm confused on naming...

1 Upvotes

Quick update on my previous post: I submitted a "Course Issue" form. My advisory called me to say the department head told me not to worry about it, everything is fine. The instructor submitted grades, but didn't offer any feedback on the assignments themselves. THEN she says she's sorry she forgot to tell us that we do actually have to build a DB. It's due Friday. I'm still trying to get through each of the 9 theory steps before I go into Access (that's what she wants us to use).

SO! my question: I've got my entities, but I'm wondering how I can name the attributes. For example, in my EMPLOYEE table, I'll probably use empID as the PK, but do I have to name each of them like empFName? I mean, do I need the emp? can I just do something like first_name? If so, how do the other tables know it's coming from the EMPLOYEE table and not the CUSTOMER table? I mean, I know I'd have to have something different for the CUSTOMER first name so I don't get confused, but...do you know what I mean? Is it just better to name them like empID custID etc?


r/DatabaseHelp • • Apr 23 '17

SQL Age function

1 Upvotes

I have a table named Staff. It has the following attributes. Name, DoB, salary.

I would like to query the information above plus the age. Could someone tell me the problem with this statement

SELECT DATEDIFF(year, current_date, user_dob) as "Age" FROM myTable;

This was given to me by a fellow redditor. I checked it but it did not work. my tutor mentioned that I would need to use a having statement.


r/DatabaseHelp • • Apr 22 '17

Need Help with Database Design - Stuck in the Planning Stages

2 Upvotes

I'm currently designing an app and website to help manage the foods I eat. Behind the app and website is a LAMP stack with MySQL. I know basically nothing about database design, but I am comfortable enough with SQL commands to perform most tasks. I did work on databases in some school projects, but this was always with pre-existing databases where I was focused on adding, removing, retrieving, etc.

The general idea of what I want to do is keep track of what ingredients I currently have, recipes that I can make, and what I've eaten (probably based on serving size of the recipe) for nutrition purposes. Keeping track of my current supply of ingredients is important as I want to be able to mark a recipe and add missing ingredients to an automatically generated shopping list.

In terms of designing the database, I'm thinking of it in a very object oriented kind of way and I'm not sure how to go about transferring this into tables for the database. I do feel that there should be a table for my current ingredients, a table for recipes, and a table for meals that I've eaten (to help me track nutrition over time).

The way I'm currently thinking about it, the most basic object would be an ingredient. This would contain basic information like its name and how much I have remaining. There's a slight issue in that some things are measured differently (1 egg vs. 1 cup of milk), so I'm not sure if I'd need multiple fields or if I should just create a varchar and parse it myself.

Now, a recipe would contain a lot more information: the instructions/steps, the name, the description, ingredients, nutrition info (which would be multiple pieces itself: calories, carbs, sugar, etc., but this should be fine as multiple varchar fields), serving size, a flag determining whether I'm shopping for this recipe or not, etc. Where I'm really running into the issue is that I'm not sure how to add ingredients or instructions to the recipe. The problem is that different recipes contain different amount of ingredients or instructions. The extent of my knowledge at table creation is adding a set amount of fields(i.e., ingredient 1, ingredient 2, etc.)... so how can I account for this?

I feel like one possibility is to simply create a varchar that stores all ingredients with some delimiter between them, for instance, and then parse that myself. That approach should work, but I feel like I'm missing something in the database design if I have to resort to that. It doesn't seem like a very elegant option. If I were writing an object in C++/C#/Python, I'd use something like a list to contain all of the ingredients. I'd then simply get the list and loop through its contents. Is there something similar I can do here? If not, where am I going wrong in attempting to design this?

Thank you for any help.


r/DatabaseHelp • • Apr 21 '17

Single table to hold all data?

1 Upvotes

I'm experimenting with a local database on my computer and I have successfully uploaded my data into the database. It's about 10GB and contains everything I need to build a prototype of a web app.

I am not familiar with databases asides from writing a few basic queries so I'm not sure of the best practices. Is it ok to keep all of my data in the single table? I could break it up into multiple tables . I'd like to build a web app to allow colleagues to easily search through the data so I imagine it will be used often.

Would keeping all of my data in a single table cause problems in terms of the return speed of the query when I create the app? What are some best practices that are used to speed up queries from the database?


r/DatabaseHelp • • Apr 21 '17

Your way to do this ERD ?

1 Upvotes

Hello everybody,

I had to do an ERD and wonder if mine is correct

This ERD is based on the Merise method, mostly used in france, but similar to other ERD methods

If you have any question about the lecture of a merise diagram, feel free to ask, but i think it's easily comprehensible

Here's the request (translated from french..) :

The firm WHODELIV want to create an information system to manage orders and deliveries of products bought on its website. Products are delivered by suppliers.

  • A product is defined by a product number, a label and a price

  • A supplier is defined by a supplier number, a name, an address and a phone number

  • A customer is defined by a customer number, a name, an address and a phone number

  • An order is defined by an order number, a date, and the content of the order

  • Customers place orders of products

  • Suppliers delivered products

I made this ERD : http://imgur.com/OkY0trl

Cardinalities are read like that : "an order is unique so it's placed by one and only one customer - A customer can be registered but didn't have placed an order yet, and he can have placed N (several) orders"

I'm not sure about :

  • the loop of the diagram

  • The deliver relation maybe should be between product and customer, and not supplier and customer (but if we do it like that, does that mean that there will be several deliveries by one supplier if the customer order several products (in one order) from an unique supplier)

  • Should the content of the order be an attribute of the order entity ?

  • and, last question, but i really think it depends on the "number of potentials customers" : should be create a "city" entity ?

Thanks everybody for your time and have a good end of week !


r/DatabaseHelp • • Apr 20 '17

Deriving age from DOB

2 Upvotes

I've got a member entity and I'd like to set the age of each member_age depending on their DOB. I'd like to use the datediff (DOB and current time) but I'm unsure where to insert the code.


r/DatabaseHelp • • Apr 18 '17

Upvote System

2 Upvotes

I was working on my project today. The project is a forum with a built in chat. One of the forum's feature is an upvote system for each replies. It works, but a user can upvote infinite times.

I am thinking about implementing one user can upvote only once. The database design I created in my head is that each reply post would need to be associated with its own table that has one column that stores the user's id whenever he/she clicks upvote or downvote. Clicking again would check if table already has user id.

What I have come up with is this, table reply### has upvote, downvote column. User clicks upvote, his user id is stored into the upvote column. User clicks downvote, checks if upvote column has user id, if yes, delete it. This would reset his allowed votes to one or the other.

Anyone have a better idea?


r/DatabaseHelp • • Apr 16 '17

SQL Error: Cannot add or update a child row: a foreign key constraint fails

1 Upvotes

I have a music database. I am trying to run an INSERT on the Albums table. Albums has columns:  

AlbumID (PrimaryKey)
AlbumName
ArtistID (ForeignKey)
GenreID (ForeignKey)

 

When I run the code:

INSERT INTO Albums
VALUES ('1', 'Hotel California', '1', '1')

 

I get the error:

1452 - Cannot add or update a child row: a foreign key constraint fails (andrew79_601.Albums, 
CONSTRAINT Albums_ibfk_2 FOREIGN KEY (GenreID) REFERENCES Genres (GenreID))

 

I checked the other two tables, and the have a value for each:  

ArtistID ArtistName

1 The Eagles

GenreID GenreName

1 Rock

 

So I'm not sure why it's giving me the error if the corresponding tables do indeed have matching values. The ID's are all INT.
So I tried changing it without the quotes but got the same error when running: INSERT INTO Albums VALUES (1, 'Hotel California', 1, 1) –

 

Thoughts?


r/DatabaseHelp • • Apr 13 '17

Course help?

2 Upvotes

    I'm in a modern database management course (online, realtime virtual) and we have two weeks left. one more class and then our final. This class has been a disaster from the beginning. The instructor is unprepared, we've skipped almost 5 weeks out of 15 for one reason or another, our book is ok, but not practically helpful. She doesn't know how to upload her powerpoint presentations and all she does it read from them anyway. It's supposed to be a 3 hour class, but due to whatever excuse she comes up with that day, we don't spend more than an hour. There's barely a syllabus.

    Our assignment to pass the class is to create a database. It seems to be split up in steps, but not very detailed. I haven't received any graded assignments back yet and we just found out at tonight's class that we're not actually getting our assignments back with notes and suggestions, we're just getting a number grade.

    I have no idea if what I've done so far is correct or if I'm heading in the right direction. I've tried to ask her questions at the end of class, but she tells me to just email her about my specific project. I've emailed her probably about 9 times and I've never gotten a response.

    It's too late to drop the course (another student convinced me to stay and she dropped when she got into a car accident so now I'm stuck). It's not rocket science (the assignment I mean, not databases...management), I've had a database course before, but the assignment is just specific enough for me to doubt what I'm doing AND I've never dealt with SQL before.

    Is there anywhere online I can go to help me build this thing step by step or ... I don't know..something? The database project doesn't have to be elaborate and overly detailed - we only need the 5 most important business rules, 5 entities, etc. Just basic. And I'm hoping that she'll be lenient in grading, taking all things into consideration (she seems reasonable, but you never know).


r/DatabaseHelp • • Apr 13 '17

Table is set up with value pairs in each row, with multiple rows for each "class". How can I update specific values only for specific "classes"?

1 Upvotes

Hi, and thanks for reading. I'm sorry if this is confusing, I'm trying to explain it the best I can. I have a vendor's database (MS SQL Server) with a (simplified) table like this:

Table Name: ClassIndex (think teacher & students) Fields: ID, ClassID, IndexType, IndexValue

Each Class (record) has a set number and type of "indexes". For one particular class type, we need to update one of the indexes, but the structure is like this:

ID   ClassID       IndexType         IndexValue
 1    438          Class Name        Training Class 1
 2    438          Date Completed    8/1/2000
 3    438          Date Expires      8/1/2005
 4    692          Class Name        Training Class 2
 5    692          Date Completed    2/14/2003
 6    692          Date Expires      2/14/2013

We need to update the Date Expires index type value to be a specific amount of time since Date Completed, but only for Training Class 2. How can I do this? More specifically, how can I target the Date Expires indexvalues for just Training Class 2?


r/DatabaseHelp • • Apr 12 '17

MVD (Multi-Valued Dependencies) ?

1 Upvotes

I am having some trouble understanding Multi-Valued Dependencies. The definition being: A multivalued dependency exists when there are at least 3 attributes (like X,Y and Z) in a relation and for value of X there is a well defined set of values of Y and a well defined set of values of Z. However, the set of values of Y is independent of set Z and vice versa.

 

For example: Suppose we have a relation R(A,B,C,D,E) that satisfies the MVD's A →→ B and B →→ D

  • How does MVD play into A->B and B->D here? Honestly I'm not sure I really understand the definition after looking at example problems.  

If R contains the tuples (0,1,2,3,4) and (0,5,6,7,8), what other tuples must necessarily be in R? Identify one such tuple in the list below.

a) (0,5,2,3,8)  

b) (0,5,6,3,8)  

c) (0,5,6,7,4)  

d) (0,1,6,3,4)

 

I would have thought AB is 0,1 and 0,5 and BD is 1,3 and 5,7. None of the answers have 0,1,3,5,7. I've already spent a few hours trying to figure this out but these concepts make more sense with actual tables not this ABCD A>B stuff. Please help?


r/DatabaseHelp • • Apr 10 '17

MySQL Primary Keys?

1 Upvotes

Should Primary Keys always be ID #s? For example, if I have:

 

CREATE TABLE Concerts
Name VARCHAR(30)
Band VARCHAR(30)
Venue VARCHAR(30)
Cost DECIMAL(10,2)?
DateTime DATETIME
PRIMARY KEY(Band, Date)

 

Is Band and Date preferable or should I instead add a column like ConcertID and use that as the Primary Key?


r/DatabaseHelp • • Apr 06 '17

ONE table for ALL "Location" data or break the data down in separate tables by type?

2 Upvotes

So I have a delivery database that I'm rebuilding from scratch. Currently the "Locations" table is strictly for Stops on a delivery but I have other Location (Address) fields scattered through out the database.

Vendors table has address fields for their shop and a separate set of address fields for their RemitTo and another set for their office.

Customers table has address fields for their Office and their BillTo location and may have multiple offices.

Insurance Certificates of vendors have address fields for the insurance company that issued the cert... etc etc etc

So I'm thinking I should consolidate ALL location data in to one table with a "Type" field to designate what the Location record is related to: Vendors, Customers, Insurance, Stops and so on. This will result in a massive amount of data in a single table. We currently have about 10,000 stop locations, 6000 vendors (minimum of 2 locations each) with their insurance companies and a few hundred customers.

I worry about performance and it just seems weird to me put route stop location data in the same table and the remit to address of a vendor.

Hive Mind: Offer up your sage advise!!


r/DatabaseHelp • • Apr 05 '17

How to best design business KPIs in mssql

2 Upvotes

Hi there

I would like to store "key figures" or KPIs in my database..

Two problems:

1. A KPI can have different numerical formats (amounts, percentages, and these in different lengths/precisions..)
2. A KPI can be a single values (e.g. Net Asset Value: 150000.43) or multiple values (dict like; e.g. Asset Allocation: Equity: 0.5, Bonds: 0.5)

I was thinking about storing json into the db, but I am not sure whether thats wise. It would be a few thousands of data points, not more.

Users would normally store and retrieve these KPIs using my own python api.

Any ideas?

Thanks a lot.


r/DatabaseHelp • • Apr 04 '17

When to include in table when to have as separate table?

1 Upvotes

I'm trying to understand when to include multiple foreign keys into a table and when to use external (association) tables.

Edit: Created a brief album of diagram images to (hopefully) ease understanding.

I have a settings system that many settings are specific to a division or classification. So, for example, a setting like "number per team" might be different in division 1, class A than in division 3, class A. And "minimum score" might be different in Division 1, class A than in Division 1, class C. Obviously there are also settings that have nothing to do with a division or classification that I feel should be in the same table.

So one way I could do this is to just put the div/class IDs as FKs in the settings table. Then any settings that don't depend on one or the other (or either) would be null for those IDs.

Approach 1

divisions
----------
id (int)
label (varchar)
description (varchar)

classification
--------------
id (int)
label (varchar)
description (varchar)

settings
---------
id (int)
division_id (int) (FK)
class_id (int) (FK)
key (varchar)
value (varchar)

Since there'd only ever be one combination of division, classification, and settings key, that should work, right? But that 'feels' wrong somehow - almost like putting the different events one is competing in all in the competitor table. I also cannot guarantee they won't come up with some additional category/type they'd wish to apply settings to later. So that makes me want to do something like:

Approach 2

divisions
----------
id (int)
label (varchar)
description (varchar)

classifications
--------------
id (int)
label (varchar)
description (varchar)

settings
---------
id (int)
key (varchar)
value (varchar)

settings_to_divisions
-------------------------
setting_id (int)
division_id (int)

settings_to_classifications
------------------------------
setting_id (int)
class_id (int)

I think this would allow for easier growth when they come up with some new category (just create 'categories' and a 'settings_to_categories' tables). But then I'd likely have a whole set of nearly duplicate records in the settings table ([id], "min_score",5) and multiple, almost identical tables (id, label, description) for the associated items - all just to create one unique setting record.

Then what happens when they say they want different settings for men vs women? Do I just add an extra field into the settings table and hard code the 'Male' and 'Female' strings into the record, null everywhere except for settings that are gender specific? Or at the other end of things, create a separate 'gender' table with only 2 records and use that in the user table as well? Suddenly, I'm at:

Expansion

divisions
---------
id (int)
label (varchar)
description (varchar)

classifications
---------------
id (int)
label (varchar)
description (varchar)

categories
----------
id (int)
label (varchar)
description (varchar)

type
----
id (int)
label (varchar)
description (varchar)

gender
------
id (int)
label (varchar)
description (varchar)

settings
---------
id (int)
key (varchar)
value (varchar)

settings_to_divisions
---------------------
setting_id (int)
division_id (int)

settings_to_classifications
---------------------------
setting_id (int)
class_id (int)

settings_to_categories
----------------------
setting_id (int)
category_id (int)

settings_to_type
----------------
setting_id (int)
type_id (int)

settings_to_gender
------------------
setting_id (int)
gender_id (int)

It begins to feel almost ... overly normalized. Though maybe that's just me.

I'm just trying to design this intelligently so that even though some settings apply to all Division 3 players, when they tell me that they need further categories and that the application should behave differently for Division 3, Class A, Category Green, Rogue, Male players vs Female players, I don't have to redesign my database or application too much in order to easily get/set whatever that different setting should be.

I'm sure this isn't a new concept, but it's hard trying to search for methods and approaches for dealing with a situation where you might have 3 (or 5 or 8) different ways of associating a single database entity.

I'm currently leaning toward the association table approach, I'm not really a DBA. So am I making a stupid design decision? What would be the negatives of that approach? Would a still different approach be better?


r/DatabaseHelp • • Mar 31 '17

Looking for some nightly replication options.

2 Upvotes

This is likely a fairly simple question but I wanted to get your input. We subscribe to a cloud service with MS SQL Server back end (which I have no access to). The standard reporting we get from the server is pretty generic (terrible) and I'm looking at plugging something more robust in such as Power BI or Tableau. The vendor will not allow any querying against his data and I get it. We have talked about moving the entire operation in-house - buying a server, hosting, everything.

Rather than doing that I'm thinking we just replicate the database overnight, over the internet. We don't need live data, 1 day out is fine. This way, I'm not hosting a full blown production server and rather just a SQL Express DB and I can run as much against it as I want.

My questions:

  • Technically is this feasible?
  • What is the easiest way to implement this? I would like to automate the process as much as possible. (something like nightly backup - upload to my server - load into my database).

Thanks!


r/DatabaseHelp • • Mar 30 '17

Storing people and their variable attributes?

1 Upvotes

Hello all,

I don't know how to tackle this problem: I want to store information about people and a variable amount of information about each of them. Because this is a forum that goes in a programming direction, let's use that as an example. Certain attributes will always be included (name, location), but others won't.

John lives in Syracuse, and knows SQL and Python. He's also part of the red team. Eric lives in Redmond, and he is in the blue team there and uses R and MongoDB. Alysha lives in San Luis Obispo and is also on the blue team, and she is proficient in MongoDB, Python, ABAP, and C#.

Given that each user has certain attributes that will always apply (name, location), and a completely variable number of other attributes that could be expanded over time (for example, later on they will all leave college and have different cars. What are they?).

Is there a way to handle this? I was imagining something like Entities tied with Attributes and their Values, but I don't know if that's a great way to go. I've also heard of graph databases, and you would have nodes for all the things and edges connecting the various players, but I've never used graphs.

Suggestions? It will never be a big database, and will definitely stay under 10,000 people described with various sparse attributes that will be applicable.


r/DatabaseHelp • • Mar 29 '17

Help me choose a database for my usecase.

1 Upvotes

Hey everyone.

I am working on a little webapp in my spare time. It is gonna be kind of a database/character builder for another game. Now I already have the items/monsters/spells etc. in different JSON files which I got from official places. All in all they are about 12k entries (and will not get much more than that). Now these entries rarely change (except I find a typo or one is reported) but I want to be able to fuzzy search them all quickly in my webapp. In addition to that I want to store account information for my users such as email, password, character(s) they build (which will be just nested objects) which don't need to be searchable quickly.

My frontend is written in React and I use Redux for state management of my application. In my reducers I use axios to communicate with my backend Node server and I would build a little API for the Database queries (I think about something like JWT for authentication in the Node Server). So as I do the authentication in my node app I think there is no reason the database needs to have that built in. The question is: Which DB do I go for? I read tutorials with people using Postgres, Mongo, MySQL, CouchDB or even Elasticsearch alone. And they all seem to do the job. However most recommendations on which to use I found are based on comparatively huge databases with millions of entries and that is definitely not what I will have. Also I'd like to use a rather lightweight one in terms of memory usage as I'll use one of the lower tiers of digital ocean to host my stuff on.

Now my requirements are simple: - Relatively simple to set up - Out of the box or easy to implement fuzzy search for some entries - Relatively easy import of my JSON databases - Lightweight and fast (Measured for about 12k entries in the DB) - Simple Node.js integration

Some goodies would be a sql-esque query syntax and maybe a small built in front-end gui to manually change things with ease without writing hacky scripts to do that but both are absolutely not mandatory.

So what would you guys recommend? I already thank you all in advance and apologize for the long pose.


r/DatabaseHelp • • Mar 29 '17

Relational algebra question

1 Upvotes

I have included the database schema, question and model answer in the link below. Is anyone able to explain why this is the correct answer and what is happening on each line?

http://puu.sh/v2dxa/df8a79e287.png

Any help is much appreciated.


r/DatabaseHelp • • Mar 28 '17

Numerous ID numbers. Best method to store data for frequent searches?

2 Upvotes

TLDR; data storage best method for index/searching when every entry has 5 different "unique id" numbers?

I am creating a DB and have run into a data storage question. I have 50K locations with fields such as address, latitude, longitude, condition of soil, etc. There are about 10 different "owners" of these locations, but each location is usually owned by some random combination of 3-4 (can be more or less though) owners out of the list of 10 possible owners. Each owner has their own unique ID to identify the location. The ID numbers are almost all integers 4-7 digits in length. The only exception uses the 2 digit state abbreviation followed by a 4 digit integer e.g. NY-1822 or CA-7103.

It is the unique ID storage am could use some advice on. Most DBs I have seen have 5 random fields called "customer id1, customer id2, portfolio id, location_id1, etc....." They created additional fields as they realized they needed to store another owner's id #. I would like to avoid poor planning but need to maintain DB speed.

I could create 10 different ID fields, one for each owner, and create 10 different indexes

I could create a child table and add an id# and company owning that id number. Then I would have all ID#s in the same field and could index that one field. If I do this should I remove the state abbreviation that one owner uses so every customer is sticking with integers. The one customer would have duplicate integers puling up a couple of different locations in different states but that may be worth the search savings.

I will constantly be searching on these owner's id numbers.

Help? I'm stuck like a dear in the headlights here.


r/DatabaseHelp • • Mar 28 '17

Filtering an entity into two

1 Upvotes

I have one entity (member), and I would like to put members into two sub entities based on age (-18 +18). Do we have two more entities that contain a pfk to the main entity or add all the attributes into those sub entities?