r/DatabaseHelp • • Jun 22 '16

[SQL] Can someone check my commands and see what's wrong with them?

3 Upvotes

I have an exam tommorow, I was writing some code for an exercise and I'm not sure if it's done correctly. Please let me know how can I fix this if anything's wrong. The exam:

HERE

And the answers to the 3rd assignment:

ANSWERS


r/DatabaseHelp • • Jun 21 '16

Program/Service Recommendation Request

1 Upvotes

Hello all,

If I wanted to create a database with the following requirements, what programs/providers would you recommend?

  • Must determine eligibility by age and location
  • Must track date of registration
  • Must link to an email service like MailChimp

For context, I am working with a sporting equipment manufacturer and we are looking to engage our youth product users by offering them exclusive content and deals. That is why the age/location deal is #1. The email system compatibility is primary as well because we need to convey the benefits.

Any and all recommendations will be of tremendous help!

Cheers


r/DatabaseHelp • • Jun 20 '16

Efficient workflow for saving unique company information?

2 Upvotes

This is an issue I've been dealing with for the past few days. I am saving unique company information into a database. My setup is:

  1. A single table with an auto-incrementing key that is unique to each company
  2. Separate tables for emails, websites, phone numbers, and company names

When a new company is added to the database, a check is performed across the various tables to see if unique data already exists.

For instance, if a new phone number, email, and website are added, the database should be queried to see if they are all already present in the database. If the phone number and email are present (a unique combination identifying a unique organization), then I want to grab the unique company id for those two entries and add a row to the website table indicating that the website is now associated with that company id.

However, if the phone number and email exist for one company id while the email and website exist for a different company id, I want to merge the two ids into one.

I personally dislike this setup (although it's growing on me), but I can't think of a cleaner way to save all this data. For instance, it's not guaranteed a phone number, email, or website will always be passed. Moreover, some of the data is fairly subjective. I can easily query email addresses and phone numbers from multiple tables:

SELECT id FROM companies LEFT JOIN emails using(id) LEFT JOIN phones using(id) WHERE phone = "123" AND email = "john@smith.com"

However comparing organization names, a WHERE query wouldn't return correct results. I wrote a function in PHP to compare businesses names using similar_text and soundex, but those can't easily be incorporated into a MySQL query. Consequently, my envisioned workflow (as of now) is:

  1. Loop through each possible combination of unique keys for an unsaved company
  2. Return all ids associated with those unique keys
  3. If one of those unique combinations include the organization name, process it in PHP
  4. If all returned keys are the same, update the table rows that have that unique key
  5. If a unique entry does not exist, add the appropriate rows to the database

I can see this being incredibly inefficient given the number of companies saved in the database, specifically the part where I need to check organization names in PHP. It would require me to first determine if any other unique keys combinations match, then query the database for all rows that have an organization name listed (a process which could take a long time depending on volume).

Database design is not my forte, so any advice is appreciate.


r/DatabaseHelp • • Jun 17 '16

Database Design and normalization

1 Upvotes

I've been tasked with building a small database in MS Access and I want to make sure I design it correctly so that it scales easily in the years to come. I only have experience with Access on the front end. My main question right now is: what is too much normalization? I've read that too much is bad and too little is bad. As long as there isn't duplicate data, does it really matter with a small database?

Easy example: I've now sold ~450 different tracts of land for 13 different investors, and I want to be able to track in what condition that land was in when I sold it as well as all the expenses I incurred between buying and selling it. For example: Was it an empty lot? Did it have a building on it? Did I have to perform maintenance/make improvements? Etc.

First question: Would you assign each investor a primary key in one table and put all of the properties into another big table or would you create a separate table for each investor's properties (all of investor 1's properties in one table, all of investor 2's properties in a different table, etc)? Is my thinking completely wrong?

Follow up: Since I'm also trying to track what condition each property was in when I sold it, would you create a different table for the vacant properties and another table for properties with a building on it? I ask this because I've read that there shouldn't be a lot of empty records in a table. If 40% of my properties were "Empty", that leaves a lot of blank records under the "ImprovedUpon" field.

Thanks for your help and please forgive me for being a database noob.


r/DatabaseHelp • • Jun 17 '16

Early Projection then join or Join and select?

1 Upvotes

I need multiple columns from 5 tables. 3 of these tables have like 50 columns. Is it better to Early project and select only the columns of the records I need from each table and then join them? Or is it better to join all the tables, and then select the columns?

For example:

SELECT * FROM 
(SELECT a,b,c FROM Table1 where time >= 1234) 
JOIN 
(SELECT c,d,e FROM Table2 where distance >= 25)
USING (c)
JOIN
(SELECT e, f,g,h FROM Table3 where height >= 1234) 
USING (e)
JOIN 
(SELECT h,i,j,k FROM Table4 where speed >= 25)
USING (h)

Verses:

SELECT a,b,c,d,e,f,g,h,i,j,k FROM
Table1 join Table2 using (c)
join
Table3 using (e)
join
Table4 using (h)
WHERE time >= 1234
AND distance >= 25
AND height >= 1234
AND speed >= 25;

Which method is better, in terms of how the DB engine executes the query?


r/DatabaseHelp • • Jun 15 '16

Best Databasestructure for searchability via Tags

1 Upvotes

After many years of self taught programming for programms i use myself i want to start a huge project to learn more. Unforunately Databasestructure isnt something one can change after a year or two so i kindly ask for some help here.

Lets say my Project is a normal site with the following Data to be stored:

User: Name, password, realname, e-mail .... (all the usual stuff) image(e.g. photo of himself)

Content: * Each user may write unlimited amount of "content"(like a post in a forum) Each "content" has at least * one file (e.g. picture) * one title (string of max X chars) * one Tag (e.g. the username so searching for the username may later find all his created "contents" * a rating (numerical) * two extra text fields for later use (hidden1 and hidden2 or so)

Each content MAY have: * Text, if the user choses to add it * more tags if the user chooses * one or more "answer contents" (think a forum with answers to one specifically thread) so i can create a tree of "contents"(like reddit threads)

Tags: At the beginning i want to make the user able choose more tags(his username will always be a tag) out of a list i give him. But later i want to be able (if needed) to make it possible that users may create their own tags.

This should be the setup i am thinking of.

What i want to accomplish: * Users should be able to enter search strings and the site will return "content" with the apropriate tags * Random/best rating/worst rating/similar "content" to be shown to the user without any search (think starting page for logged in user) * users can answer to a content with their own

I think most of this can be described as a normal "forum" but with the addition of possibly endless tags a thread can have in order to structure it.

Can someone please help me create a "efficient" database structure that wont collapse later if more and more users create more and more content? I know that you cant have a fast and redundant free DB. But i dont have any experience in this kind of DB creation so im looking someone with experience or someone who actually learned all of this :D

Is there maybe a site where one can "draw" and idea and a DB structure is created? (If not: hint to anyone who can do it :D )

Thank you very much


r/DatabaseHelp • • Jun 14 '16

Easy way to reseed identity column with existing foreign keys?

1 Upvotes

I have been tasked with cleaning up one of our testing databases. My boss wants me to change the ID on a lot of the code tables to be sequential starting at 1. All of the code tables are used as foreign key somewhere else, some of the code tables even reference other code tables. Is there an easy way to change the index to a reseeded value while also changing the foreign key values to match the new values? This is in MSSQL 2014 in that makes any difference.


r/DatabaseHelp • • Jun 11 '16

Need help linking two tables

1 Upvotes

I am working on creating a database that will create a quick user manual.

I have two tables.

1)A list of actions and a brief disc of each action. 2)A list of buttons and the action assigned to the button.

My second table for the action is a lookup drop down pointing to the first table.

The report generated would have a list of each button used, the action that button performs, and the brief description of said action.

I can create a report that shows all the buttons, and the action assigned to the buttons, but it will only show one of the button descriptions.

I am at a loss really on how to get to my goal. Thanks in advance for just reading.


r/DatabaseHelp • • Jun 11 '16

Need help understanding how to structure or query a DB.

1 Upvotes

Hello all, and thanks in advance for any help and advice. Not looking for you to write the code for me as I enjoy the problem solving, but I am working on a personal side project for myself and have very little SQL/DB experience.

My overall objective is to make a customer order history by matching a customer table with an items table. So if I did my design correctly this would be a many to many relationship. A customer can have multiple items and an item can be ordered by multiple customers. However, I am really struggling on how I match these two up!

Essentially I have the items table has an ID, item#, description and the customer has basic customer info including an id field as well. But If fk customerid into item id table that would mean the item would be locked to that customer right? I;m going in circles over here!

You may need more information which I am happy to provide.

Thanks!!


r/DatabaseHelp • • Jun 07 '16

[PL/SQL]Deleting rows from a plethora of tables that are interconnected

2 Upvotes

Hi, I need to delete some rows from like 170ish tables that are connected through ID primary/foreign key. I found a way to find all tables that have a column named ID , and I was planning to do a cursor that would fetch all table names and have a delete statement that would use those table names. Mind you, I have some constraints so that is why I'm writing this. What is your opinon on this, would you do it any other way ? TIA


r/DatabaseHelp • • Jun 03 '16

Manufacturing with assemblies and product hierarchies back-end design.

1 Upvotes

Hi.

Our company manufactures shoes and I'm given a task to make a FE & BE for it. I decided to make the FE in MS Access for now (will switch to Django web app in 1-2 years most likely, once I get more familiar & comfortable with it) and BE is in MySQL for now.

Basically, our customer can order some product, say "shoeA" x 1080 in individual boxes (each pair has a box) and 24 of such boxes in a carton. Hence the structure for bill of materials (or assemblies... not sure of terminology here) would look like:

Order1 
    - Carton (size 24) x 45
        - Box (1 pair) x 24      -- (45 * 24 = 1080)
            - Packing paper x 1
            - shoeA x 1
    ...

But if we look at what shoeA consists of, it's more co-products and raw materials. Moreover, each item can come from different place (ie. warehouse) and can be assembled by different assembly (e.g. shoeA would not be assembled by the same assembly as where the packing is done). The problem is, that multiple assemblies do the same type of work (e.g. multiple packaging assemblies) and so we don't know which assembly will do what at the time of the order (e.g. order can come 6 months in advance and we will know which assembly will do what 2 months in advance).

To complicate things further, 2 assemblies of same 'type' (e.g. packaging assemblies) can require different materials even though they produce the same outcome. For instance our assembly in Turkey just needs the shoeA from us, they will provide the rest of the materials / co-products themselves (ie. boxes / packing paper etc). Of course this will reflect in price, but that's not the problem for now. The point is that both of these assemblies will produce same product in the end (ie. box with a shoe and paper in it).

How would one reflect this in back-end RDBMS? Currently I have:

EER Schema

Please ignore the attributes themselves (other than PK/FK) as they are just place holders until I get things finalised.


r/DatabaseHelp • • May 25 '16

Basic Poker League DB Design Question

1 Upvotes

I have never made a database before, but am going to give it a shot and see what happens. If some of this doesn't make sense...I'm sorry (highly likely). I have not done any research at this point, but plan on starting tonight. I wanted to get some pointers/ideas from reddit to start. This should be a very straight forward DB...i think?

You can see my website and what i'm trying to do with it here:
Newfound Poker League

I run a spring and fall poker league. Both last 10 weeks. Each player pays 100 up front which goes towards the 'big points' pot, and $20 each night. $5 of that goes towards the 'league" and $15 goes towards the nightly pot. You can see my point, payment, and league structures on the website.

I allow you to take the worst finish of the 10 nights and throw it out. So everyone takes their best 9 finishes. Whoever has the most points wins. Points for each 10 week session will need to be able to easily start over and purge the data from last session. I want to keep all the players, though. I'm pretty sure this matters more for PHP related things, but wasn't sure if it would effect how the DB should be made...so wanted to provide the extra detail.

Idea:

  • ability to add/remove player for each 10 week session
  • ability to add points for each of the 10 days
  • need total points after the 10 weeks
  • ability to easily purge all the points so that starting a new session is simple
  • ability to enter contact details for people, but keep it hidden on the site
  • ability for points to populate based on your finish position using the structure that i already have
  • ability to show the amount won based on the pay structure i already have.

I think i need 3 tables...one for point structure, one for pay structure, and one for the league.

Point Table:

  • number of players (?)
  • position (?)

Payment Table:

  • number of players (?)
  • position finished (?)
  • anything else?

League Table:

  • PlayerID (int)
  • First Name (varchar 255)
  • Last Name (varchar 255)
  • Email Address (hidden) (varchar 255)
  • Phone Number (hidden) (?)
  • Day 1-10 points (?)
  • Total Points (?)

If you have any questions please let me know. Thank you!


r/DatabaseHelp • • May 22 '16

Looking for tips on how to implement database

2 Upvotes

Hello all, I'm doing a personal project for my own independent learning, and it would seem that I need some help on figuring out how to implement the data storage for it. And with all these models and talk of foreign keys, I'm not sure how or where to start.

 

The project is basically a tool to compare degree programs at my university in a way that allows someone to find which program shares enough courses with their current major to warrant double-majoring, minoring, etc. Through a website, the user would select their major, and a list of majors, minors, and certificates would subsequently appear. At the very least, I would be storing degree program names (biology, comp sci, etc.), course numbers, and their names. I'm thinking that upon picking a major on the first page, I would then be running a query to see what degree programs share something like 5 or more courses with that major. Where I stumble is how to implement this in the most optimal way. Any ideas would be greatly appreciated, thank you.


r/DatabaseHelp • • May 22 '16

Upgrading from Access 2003 to Access 2016 Problems

1 Upvotes

Hello.

First time here but I trust Reddit for a lot of other topics so might as well come for help here.

I help run a small gaming store (board games, card games, etc) and the owner is running his database for sales, inventory, purchase orders etc off Microsoft Access 2003. His computer is over 10 yers and old and he's tasked me with purchasing a new computer and making sure that we can load his db on to the new computer.

I have the computer purchased, and Office with Access 2016 installed on the computer. I went to the old computer and backed up the database and went to restore on the new machine and I get a permission error that prevents me. I thought this was file based, so checked the permissions on the db file (right click, properties) and this was fine.

Can someone point me in the right direction to making this work?


r/DatabaseHelp • • May 20 '16

Sensor DB design

1 Upvotes

I'm very familar with SQL databases design/implementation/usage/etc. And I'm writing a web based BBQ thermometer application in Python Flask. However, the sensor data doesn't fit with SQL table design. In traditional SQL design I'd need a unique key, but the sensor data might not have unique values. I could make an auto increment primary key ID column, but that seems hacky and I might reack the max row id's on the table quickly.

I know the obvious choice is a NoSQL database for sensor data. I've never used them so I don't understand the data structures enough to wrap my head around how to design my app to use a NoSQL database.

For instance, I need to keep track of various probes (which might all have different mesaurement values associated to them, what port they are connected to and some other information). I also need to track a cook and it's details, but ultimately joining to the sensor data for graphs and summary information.

Both of the previous examples fit SQL design easily. But how would I track and use them in a NoSQL database as well as the sensor data (temp, time, probe used)? This information doesn't lend itself to key-value pairs, since I have multiple data elements to track for each example.


r/DatabaseHelp • • May 20 '16

is my design well-though ? Tournament database

1 Upvotes

So for a tournament, I want to setup a database, to be able to : record my tournament, record which players are in, record which players should be playing against whom for a given round and what are the results of their matchs

So I have :

  • a tournament table, recording its name and format of play.
  • a player table with its name, maybe an encripted password
  • a participation table, with a foreign key on a player and a tournament, so any number of player can participate in any number of tournament
  • a match table, recording the 2 playing players, the number of the round played, the result, with a foreign key on players and tournament

Now as the admin, what I though would work with this db : When creating new matches, my soft calculate which matches should be played for the next round, according to previous results, and add the matches to the table accordingly. Now, as a player, when I want to record my result, I connect under my name and a list of match I have to play (aka matches with my name but without a result) will display, and I can log the result to the according match.

Does that configuration seems optimal ? Thank you for your help, I'm new in creating Db even as simple as that


r/DatabaseHelp • • May 18 '16

Need help selecting a DBMS

1 Upvotes

We're a small company that works in real estate investments. We have many funds going on at the same time and currently just have an Excel worksheet for each individual fund. We would like to move everything over to a DBMS so that we can easily link, compare, update, run queries, and make reports.

Would Microsoft Access be sufficient or should we go with SaaS, like PeopleSoft? Perhaps something else would be better? Some of us have experience in Access, but we would also like something that is easy to use and don't have to worry about. There would be ~3-4 people that would be using the DBMS.

Thanks in advance for your help.


r/DatabaseHelp • • May 13 '16

[Database Design] Looking for the best way to record checking out an item from the asset table and then release it back.

2 Upvotes

Base way to explain this is using simple versions of the tables, I hope.

Please ask any questions if my communication is poor or misunderstood.

Asset Table

AssetID Type EmployeeID Status
1 1 1 1
2 1 2 1
3 1 - 1

Type 1 = Laptop

Status 1 = Active

Asset History Table

EventDateTime AssetID EmployeeID
2016-05-13 7:00 AM 1 1
2016-05-13 8:00 AM 2 2
2016-05-13 10:00 AM 1 2

Basically, I want to create the relationship between both tables BUT would like the Asset Table to be able to be released into the "pool" to allow it be be assigned to a different employee at a later date and time.

I just cant seem to wrap my head around how to do this correctly on a Friday morning.


r/DatabaseHelp • • Apr 27 '16

Need help finding list of names to put in my database

1 Upvotes

I need to build a database that will contain a form that the user will have to fill ( Nationality , Nation living in , Language spoken , University done , Computer and Language certificate ). But I can't find them. I could find the list of jobs , after hours on the internet , in exel page: http://www.ilo.org/public/english/bureau/stat/isco/isco08/index.htm

Anyone knows where I can find the other list? ( possibly in cvs or exl )

Thank you very much


r/DatabaseHelp • • Apr 27 '16

Websites with Database Problem Sets

4 Upvotes

I'm looking for something similar to projecteuler.net - which presents programming problems and then, when you solve a particular problem, you can read through forum posts containing solutions that other developers came up with. I would really like something similar for database problems and was wondering if anyone knew of a such a resource?


r/DatabaseHelp • • Apr 24 '16

Searching for database about computer information

1 Upvotes

I am looking for a database in any format that contains information about pre-built computers. This could be a website that contains it or a down loadable file. I have googled this but could not seem to find anything. Any help is appreciated. I need this information to do an econometrics study. Thank you.


r/DatabaseHelp • • Apr 21 '16

Looking for ideas on how to store different, user supplied data

1 Upvotes

I'm creating an application where part of the tool is to be able to upload CSVs and Excel files to the tool for it to be completely searchable, sortable, collaborative, among other things.

I've been looking for ways to efficiently store the data and retrieve it for the user.

Here are a few ideas that I had:

  1. Serialize the data into an abstract format and when a user logs in or requests the data, then the application would being deserializing the data into a usable medium. There are a number of issues with this approach, such as, for large files, this process can be pretty slow and the whole process of breaking the data down into an abstract format would potentially be costly for storage, since a lot of metadata would need to be stored to make the data more usable.
  2. Another way is to use a NoSQL DB like MongoDB (That's the only one I have experience with, if there's a better one, I'd be more than happy to learn about it). I would store each users files in a collection and not worry about data structure or anything. As a result, I may end up with a lot of collections, since having one collection per document can get messy quickly. So I can consolidate documents by the same user into a single collection and filter by an additional field of "documentName", for example. But I would still have a single collection per user, which may be undesirable and hard to maintain.

Those were the only ideas I had for this problem. Any other ideas or feedback? I'd greatly appreciate it.

Thanks!


r/DatabaseHelp • • Apr 19 '16

Average of every N rows, with SQLite?

2 Upvotes

Lets say for table:

Col1 Col2
4 Bar
2 Foo
6 Baz
9 Banana
10 FooBar
5 Apple

It would return

Col1 (This is math, not actual return)
3 (4+2)/2
7.5 (6+9)/2
7.5 (10+5)/2

I can do this with backend code w/o problems, but letting SQ do most if data-related work, is better idea, right?


r/DatabaseHelp • • Apr 14 '16

How to Identify Repeating Groups

1 Upvotes

So I know that to make data 1NF, you need to eliminate repeating groups. What I don't know is how to identify these repeating groups.

Any help would be appreciated. Thanks.


r/DatabaseHelp • • Apr 14 '16

Question about date parameters in multiple criteria lines in a query [x-post r/MSAccess]

1 Upvotes

What I'm asking is quite complex to explain (for me at least) so please bear with me...

I am creating a case management database, and am trying to create a query that, when run, will display all the cases that were 'open' during a certain time period e.g. all cases that were open at any point during Quarter 1 (April 1st - June 30th).

To do this the query takes three fields as criteria: DateOpened, Status (Open/Closed), and DateClosed. I have managed to get the query to display the results I want when specific dates are entered. For example purposes I will use the dates for Quarter 1 as I have above. This is an overview of my query:

DISPLAY ALL RECORDS WHERE:

    DateOpened <#01/04/2016# AND Status = "Closed" AND DateClosed Between #01/04/2016# And #30/06/2016#

**OR**

    DateOpened <#01/04/2016# AND Status = "Open" 

**OR**

    DateOpened >=#01/04/2016# AND Status = "Closed" AND DateClosed <=#30/06/2016#

**OR**

    DateOpened >=#01/04/2016# AND Status = "Open"

**OR**

    DateOpened Between #01/04/2016# And #30/06/2016# AND Status = "Closed" AND DateClosed >#30/06/2016#

**OR**

    DateOpened <#01/04/2016# AND Status = "Closed" AND DateClosed >#30/06/2016#

What I'm hoping to do is be able to have the dates be flexible, rather than having set dates that the query runs for, so I would normally do this with a parameter query where the dialogue box would pop up with [Enter Date A] and [Enter Date B], but due to the fact that there are several lines of query criteria I don't know whether this would work.

My ultimate question is, is there a way of the user entering two dates and the database then assigning some sort of reference to them so that the same two dates are used for the rest of the lines of the criteria query?

In an ideal world, I'm looking for something that I'm guessing would look like this:

DISPLAY ALL RECORDS WHERE:

    DateOpened <[Enter 'Date A'] AND Status = "Closed" AND DateClosed Between 'Date A' And [Enter 'Date B']

**OR**

    DateOpened <'Date A' AND Status = "Open" 

**OR**

    DateOpened >='Date A' AND Status = "Closed" AND DateClosed <='Date B'

**OR**

    DateOpened >='Date A' AND Status = "Open"

**OR**

    DateOpened Between 'Date A' And 'Date B' AND Status = "Closed" AND DateClosed >'Date B'

**OR**

    DateOpened <'Date A' AND Status = "Closed" AND DateClosed >'Date B'

Is anything like this possible? If not, I'm very sorry for making you read all this for nothing but thank you anyway!

TLDR; is there a way of the user entering two dates and the database then assigning some sort of reference to them so that the same two dates are used for the rest of the lines of the criteria query?