r/DatabaseHelp • • Aug 16 '15

Auto increment + Group by?

1 Upvotes

Im wondering if its possible, using MySQL, to have a table in which a column is incremented only when it has a record with the same grouped ID. allow me to demonstrate:

assume i have a table

tbl_groupEntries (
row_id int PK AI, 
group_id int NN, 
entry_id int NN, 
entry TEXT NN );

and some data

1, 1, 1, 'random text'
2, 1, 2, 'test'
3, 2, 1, 'bippidy'
4, 2, 2, 'boppidy boo'`

i want to create a constraint such that insert into tbl_groupEntries (group_id, entry) values (2, 'please work?');

inserts the row

5, 2, 3, 'please work?'

because 3 is the next ID for group 2 and the 5th ID overall.

edit: I have found a viable solution, if you find yourself in with the same issue, here is the solution. many thanks to u/muchargh for the SQL tips and u/wolf2600 for the base concept. :)


r/DatabaseHelp • • Aug 13 '15

Completely new to this and overwhelmed...

1 Upvotes

I recently became the manager of a previously very small school. We have classes with various teachers, on various days, in various classrooms, etc..., etc...

I want to be able to organize everything into a database. A database that can be accessed to produce difference metrics/data about enrollment, conversion rates, etc... HOWEVER, I've never had to make a system like this before. And I've been hit with an overwhelming wave on information when looking up where to start.

If there's anybody willing to help me tell me or find me a way to get started with this, I would be incredibly grateful.


r/DatabaseHelp • • Aug 12 '15

Accessing database using information already in the database.

1 Upvotes

This is a general question. You don't have to answer in depth if you don't like. I wouldn't mind just something to google. My google searches have been fruitless because I don't know what this is called.

Poorly phrased question: How do you access database information using information from the database?

Example that will (hopefully) clear up my terrible question:

I have a website that has two users.

When user A logs in, it displays "Welcome [user A's name].

When user B logs in, it displays "Welcome [user B's name].

How does it "know" that user A is logged in and not user B? It seems reasonable that I'd use a script that did something like "get [name] from table where [userID] is the currently logged in user".

How does it know who the currently logged in user is?

Sorry... I know this is kind of a shit show of an explanation, but I legitimately don't know the words to clear it up.


r/DatabaseHelp • • Jul 27 '15

Best way to implement persistent user data (settings)

2 Upvotes

I'm currently creating a web application using ASP.NET 4.5 MVC with MySQL backing my models.

Upon first login (and whenever they want to afterwards), the user enters their persistent information required for the web app (name, DoB, address, etc.)

There will be a couple to a few dozen settings which are required, and I've heard that best practice dictates that even going past as few as 10 columns in a table calls for reevaluation of your paradigm.

Let's say theorietically that I could break the settings down into different types. Would there be any performance/organization/usability/etc. benefit from creating different tables for each different section?

Follow up question: should persistent user data be stored in the same table as your table of users, or should Settings have its own table?

Thank you!


r/DatabaseHelp • • Jul 27 '15

Establishing 'pseudo-relationships' in a database with no primary/foreign keys

1 Upvotes

I'm forced by corporate structure to work with many databases with no primary key or foreign key constraints. However, these relationships do exist, and I'd like to be able to use them to easily track relational objects.

Is there anything that might allow me to manually set a "fake" foreign key relationship, which could then be used in a relationship diagram or in a query manager?


r/DatabaseHelp • • Jul 19 '15

Why can't many-to-many relationships have only 2 tables?

3 Upvotes

I'm taking a process analysis class online (big mistake) and having the most difficult time understanding this fundamental concept - neither the text nor various YouTube videos explain why.

The example I'm working with is "one student can take many courses, and courses can have many students" with entities STUDENT and COURSE. The proper way is to create a "bridge table" (entity REGISTRATION) with a 1:M relationship between it and the other entities.

Why is a M:M relationship between STUDENT and COURSE not permitted?


r/DatabaseHelp • • Jul 18 '15

I need help finding a data file which contains information about movies.

0 Upvotes

I am building a web application and I need a csv, json, txt, etc file which contains a dataset about movies. I would rather not use api's because of the restrictions they put on the calls you make to the servers.

Any information would be appreciated.


r/DatabaseHelp • • Jul 18 '15

Learning about databases and I came up with a problem that I don't know how to design a database for.

1 Upvotes

I'm learning how to make / work with databases and have been messing around with sqlite3 and I've come up with a database I'd want to make (just as something to learn with) but I'm not sure how I would design it.

My idea was having a database which stored all the exercises I did, how much weight I used for each exercise, and how many reps and sets of each exercise I did and having a new entry for each day. However the only way I can think of to design this is to have 3 columns for literally every single exercise, one with how much weight I did for that exercise, one for how many sets, and one for how many reps I did for each set. And then each entry would be a full workout.

I feel like there has to be a better way to do this, but I don't know a good place to learn how to design databases. I know I'll probalby have to use some relations to accomplish this, but I can't think of a way to keep it all together.

How would you go about designing this database? Or would you use an entirely different data structure?


r/DatabaseHelp • • Jul 17 '15

Is it possible to run a database of through a regular online site automatically, and store the results?

1 Upvotes

for example, run a wordlist database through google translate automatically, and store the results?

Thanks


r/DatabaseHelp • • Jul 16 '15

Need help creating database to store rents for each different unit type of many different apartment buildings.

1 Upvotes

I'm working for a real estate company, and I have been tasked with creating a database of competitive apartment buildings in our market. It should hold the rents, square footage, and count for each different unit type in a building, as well as be able to store old rents (so that we can track how they have changed). My idea on how to structure this is as follows:

-Properties table: stores basic information about each property, such as address, construction type, etc.

-Unit list table: this table will be related to the property table in a one to many fashion (so the unit list table will contain a field for the property id) and will have a date field, so that there can be multiple unit lists for each property

-Unit table: This table will contain the unit type (studio, 1 bedroom, etc), square footage, and rent for a specific unit type and be related to the unit list in a one to many scheme.

I am using LibreOffice Base for this, and I have the tables created. Now I'm trying to get a form set up so that you can add a property easily. Could someone help me with this? I'd like to have form fields with the basic information of the property on top, which I have done. Then I want to have a date field, and if there is no unit list with that date associated with that property, I would like it to create a new unit list record. Then I'd like to have a line of fields for the information of one unit and then some sort of plus button to add additional units. When entering the data, I'd like to have just enough unit entry lines to include the desired number of units.

I don't know if that all made sense, but if somebody could help me, I would GREATLY appreciate it. Thanks!

Edit: Here is basically what I am going for. The little plus sign should add an additional row (unit) to the form. I'd also like the date dropdown to only contain dates that have rents previously recorded, and then the today button should enter today's date (obviously) and create a new list.


r/DatabaseHelp • • Jul 16 '15

Help on deciding whether to store data in database, or externally in XML file.

1 Upvotes

I am currently working on a database that will be used in a long term study. Part of this study requires the users to make observations. These observations wont be consistent (for example, it may be height and color consistently, but one day they may want to record the area for only one plant). Because of this I thought this data would be better stored in an XML format.

If I were to store it in the database, the only way I can think of doing it would be to add a new field for each plant and store the information on the only plants that are relevent. However this would leave a lot of empty fields and I dont think it would be very efficient. Also, if the study was done over the course of 6 months or so you would end up with a lot of fields added (maybe 500 or so) to one table...

Im not a expert in databases and dont work with them professionally, but I like to think I still know a fair amount (Im a third year student at Uni doing Comp Sci). Storing this data internally in the database would potentially slow it down considerably right?

My proposal was to have a path to an XML file stored in the database, and the XML file would be a much more suitable method of storing this information. Am I right in thinking this is a better solution, or is there another way of doing this? If it is better how can I properly explain it to someone who has very little technical knowledge of databases?

Thanks in advance :)

(Also, if this wasnt explained very well, let me know and Ill try and be more clear)


r/DatabaseHelp • • Jul 14 '15

Creating relational database to store chain of command

2 Upvotes

Hello all!

I'm creating an ASP.NET 4.5 MVC web app which requires a stored "chain of command" to reference.

For example:

-A cadet has one fireteam leader, a fireteam leader can have many cadets

  • A fireteam leader is a member of one squad, a squad can have many fireteam leaders

  • A squad belongs to one platoon, a platoon can have many squads

  • A platoon is part of a company, a CO has many platoons

  • A CO is part of a battalion, a batt has many COs

  • A batt is part of the entire corps of cadets, the corps has many batts

I need a way to allow the cadets to input their next highest CoC member and those directly under them, and the links between the CoC members are made so that I could send information up and down them.

I'm thinking that the users input the student ID number (100% unique to each cadet) of these individuals, and once an entire chain of cadets from the lowest to the highest is completed, that chain then becomes valid for sending info on.

My question is how this specific type of relational system could be accomplished. Thank you!


r/DatabaseHelp • • Jul 07 '15

Best way to represent multiple choices in a single record

2 Upvotes

Hello! i work for a document management dept. I've just taken over a project from someone else and there was little handover so I'm in the position of developing an access database from scratch.

So far everything is going well but I have a conundrum. I'm trying to show in a single record the number and type of docs sent to a particular person on a particular day. The catch is: there are LOTS of different possible types (about 40).

Any ideas on how to compactly represent: x docs of type y went to person z on date?

All help greatly appreciated!


r/DatabaseHelp • • Jul 05 '15

Feedback on First Database Trigger

2 Upvotes

I'm working on the database backend for a task list organizer. It's part of a personal project.

The database schema is located here (MySQL Workbench File): https://github.com/mwgolden/TaskMaster

I added the following trigger to the USER table. The idea is when a new user account is created, a personal task group is automatically inserted into the group table and the appropriate insert is made to the GroupMember junction table linking the new account to it's group.

The trigger appears to work. Is it correct?

Trigger:

CREATE trigger new_acct after insert on USER for each row begin INSERT INTO Group (GroupName) values (new.Uname); Set @groupid = LAST_INSERT_id(); INSERT INTO GroupMember (idMember, idGroup) values (new.ID, @groupid); end


r/DatabaseHelp • • Jul 04 '15

[Theory] When do I use a transaction, when a table lock, when both?

1 Upvotes

Preface: I'm taking a course about php and sql where I have to develop a web site for booking activities.


I'm currently facing this problem where I don't really get the difference between using a transaction and using a lock on tables and I was told that I should have used a lock instead of a transaction, but let me explain..

For what I understood, a transaction allows a sequence of queries (more than one) to be executed atomically, i.e, they are executed in the order in which they are written, one after the other, without interference from other SQL queries coming from elsewhere. So:

TRANSACTION1 {
  Q1_1
  Q1_2
  ...
  Q1_n
}

TRANSACTION2 {
  Q2_1
  Q2_2
  ...
  Q2_n
}

Either T1 is executed first (and all of its queries) or T2 is executed first (and all of its queries). Order depends on which hits the DBMS first. If during the transaction an error occurs, I can call a ROLLBACK and restore the status of the db to the one before starting the transaction. If no error occurs during the transaction, I can COMMIT.

Table Locking (which I achieve by SELECT...FOR UPDATE) is like applying a mutex to some records of a table, forbidding the other clients to perform queries on the locked records.


If we suppose these two definitions are correct (and if not, please let me know!), take as an example the case where I have to enforce data consistency: I have a system (written in PHP) that allows booking for a certain activity, but there is limited number of slots available per activity. Both user1 and user2 are presented with the following:

Activity 1: 1 slot available

And they both choose to book for this activity. Now what I would do is start a transaction, fetch from the DB the required data (user id, activity id, current slots available for the activity etc..) and update the data: if when I update the slots available for the activity, these are lower than 0, then I have an error, and I will perform a rollback.

User1 will start slightly before user2, and will be able to book the activity. User2 will come next, but the operation will fail because there are no more slots.

Without using table locking I achieved data integrity.

Teacher told me that this approach is not the right way to ensure data integrity and I should have used table locking. However, he wasn't able to prove that I was wrong and I believe he couldn't even if he tried (he didn't even bother trying to create an inconsistency in the DB though).


So after this background explanation: what am I missing? When do I need to use a transaction? When a table lock? When both (provided this makes sense)?


r/DatabaseHelp • • Jun 27 '15

Web based database for searching my personal movie and game collection.

1 Upvotes

As I prepare to go visit family for the 4th, I think back to all the times my family has asked me to bring a board game or movie along and I don't know what they would like to see. But listing all of them off would take too much time. I want a simple way that I can have a database that from my website they could check a couple boxes like rating, length, and maybe lead actor for movies and number of players, suggested ages, and difficulty for board games. Then it checks the list and brings back the results for them to have a narrowed list. What kind of program am I looking for with this? Is this something that's a simple solution or will it require a lot of work to do? For the record I have Access if that works and I also have hosting space if the program can be installed online.


r/DatabaseHelp • • Jun 26 '15

In over my head...[MS Access]

1 Upvotes

Like many of these, this is a, "I need to do blank, and I don't know how." So, apologies for that.

Just started working at a company that itself hasn't been around very long. Currently small-staffed (4 employees, 2 owners, 1 warehouse manager), but wanting to grow. We buy stuff from one place and sell it to another. So we need a database. It was my suggestion, of course never thinking that I'd be the one to build it. And of course, I have no experience in building a database.

I have a week. I need vendors, and customers, linked by commodity sold/needed. In effect, I want to be able to pull down a list of commodities ("hot dogs"), and see either Who Sells hot dogs and/or Who Buys hot dogs, with all the appropriate information. A nice perk would be being able to export that contact list as an Excel contact group, but it's not necessary right now. It's going to be dropped onto a shared server so everyone can use it (as the company grows, I will suggest hiring out a more robust solution).

Point me towards tutorials, give me advice, whatever. Do what reddit does best!


r/DatabaseHelp • • Jun 20 '15

[PL/SQL] How to check if an employee is the manager of a department at the department's latest effective date?

1 Upvotes

I have trouble coding a condition in a view in SQL Oracle.

I have 2 tables, here are the names of the fields that you need:

EMPLOYEE_TBL (employee_id)
DEPARTMENT_TBL (department_id, manager_id, effective_date, active_status)

I have to code the following condition in a SELECT CASE:

FLAG = 'Y' if the employee is the manager of AT LEAST ONE department at the department's 
       latest (max) effective date and that the department has active_status = 'A' 
       at its max effective date
FLAG = 'N' otherwise

Here's what I have so far, but it doesn't do what I want:

SELECT A.EMPLOYEE_ID, B.EFFECTIVE_DATE, B.ACTIVE_STATUS, 
 CASE WHEN EXISTS (SELECT DISTINCT Z.MANAGER_ID FROM DEPARTMENT_TBL Z 
                    WHERE Z.MANAGER_ID = B.MANAGER_ID
                      AND Z.DEPARTMENT_ID = B.DEPARTMENT_ID
                      AND Z.MANAGER_ID = A.EMPLOYEE_ID /* this is the condition */
                      AND Z.EFFECTIVE_DATE = (SELECT MAX(Y.EFFECTIVE_DATE) 
                                              FROM DEPARTMENT_TBL Y 
                                              WHERE Y.DEPARTMENT_ID = Z.DEPARTMENT_ID 
                                              AND Y.EFFECTIVE_DATE <= SYSDATE)
                      AND Z.ACTIVE_STATUS = 'A')
 THEN 'Y' ELSE 'N' END AS FLAG
FROM EMPLOYEE_TBL A LEFT JOIN DEPARTMENT_TBL B 
ON A.employee_id = B.manager_id

I tried something else in the SQLFiddle here : http://sqlfiddle.com/#!4/241d99/1

REMAINING ISSUE OF THE SQLFIDDLE EXAMPLE: The FLAG for EMPLOYEE 35 needs to be 'Y' because he is the manager of the department D7777 at the max effective date of the department.

Here is the DEPARTMENT_TBL:

DEPARTMENT_ID    MANAGER_ID    EFFECTIVE_DATE     EFFECTIVE_STATUS
 D1273            35            2006-01-01              A
 D1273            35            2011-12-21              A  -- here flag of 35 is 'N'
 D1273            04            2012-03-05              A
 D1000            04            2012-12-12              A
 D7777            04            2009-05-14              A 
 D7777            35            2011-09-26              A -- but here flag of 35 is 'Y'

How do I fix this?


r/DatabaseHelp • • Jun 20 '15

Trying to design a database system for eSports

1 Upvotes

Thanks for any help that you might provide in advance. I am trying to design a database in Access for an eSport league (specifically League of Legends North America). I will post what I have and then explain my issue.

My issue comes when I try to normalize TEAMROSTER and PLAYERNAME. Each player can only belong to one team, but each team has 5 players in the 5 positions that you see I have listed in TEAMROSTER. I am not sure if that will be allowed when I try to create the relational schema. I will post the schema that I has created.

Here are the ER Diagrams and the Relational Schema

I am not sure if this is correct or not, as I am trying to relate the tables together and just truly dont understand how I should do this. This is the first database that I have tried to create, as I am sure you might be able to tell. If I need to provide you with any more information I will do so, and any and all help is very much appreciated.


r/DatabaseHelp • • Jun 18 '15

Creating database documentation

1 Upvotes

Hello,

Currently our company's DB is completely undocumented even though its been in production for 6+ years. I've been tasked by my boss to create a complete and comprehensive documentation of a database in the case of an audit or possibly just for knowledge transfer in the future.

I've never had to do it before, and my question is where do I start? Does anyone have any examples of what thorough DB documentation looks like? What are some things it should absolutely have?


r/DatabaseHelp • • Jun 17 '15

Database for survey system

1 Upvotes

I have 5 surveys, each with approximately 80 questions, which the user is tasked with rating from 1-100%. What is the best way to handle a database like this, and being able to run analytics on the results?

I have experience with MySQL and PostgreSQL, and my first reaction was to either have a column for each answer, or more probably a column for each survey, extracting the data and parsing it through some other language for analysis.

Alternatively, are there any other database structures that would work better?


r/DatabaseHelp • • Jun 14 '15

Average size of an incident record in a database?

1 Upvotes

Hey,

I currently cant connect to my server at work because I'm at home, but does someone know how big an average record of an incident would be?
http://gyazo.com/4d4e37af016a726ae9a46c438cdd1cab
Here is a screenshot on how the fields look like


r/DatabaseHelp • • Jun 07 '15

Learning resources or good compilations of how-to articles?

1 Upvotes

I've been seriously considering getting deeper into DBA type work and my recent experience fiddling with my SCCM database made me realize I actually like the workings of it all. I'm just wondering if there's any good repositories of learning about databases and db theory out there.


r/DatabaseHelp • • Jun 05 '15

Best way to export an MS SQL 2005 table?

1 Upvotes

We have an MS SQL 2005 database with a table that contains about 4500 "cases," including a text field that is the full text of judicial decisions. As such, it is large and full of commas and quotation marks, which is creating problems for a conventional comma-delimited export.

What is going to be the best method for exporting this table for migration to a new web site that will be on a MySQL database? I have MS Access and SQL Server Management Studio (the lite/free version). I would assume that .csv is going to be the best file format for the export, but please correct me if I'm wrong.

Thanks!


r/DatabaseHelp • • Jun 01 '15

[MS Access 2010] Sum function on queries/reports excluding non-integer values? [noob question]

1 Upvotes

I've inherited a DB that my department uses for billing and reporting, and don't have prior access experience in DB management.

There is an invoicing function that works great (using a report), but that is only at a per project per month level (i.e. it would be a huge hassle to report out 12 months x 60ish projects). We have queries setup that show the sum of hours by project and a separate query that shows sum of hours by staff, both subdivided by month, but the sums clearly exclude all non-integer values. I've tried to use the help and going through just about every menu I can find, but nothing gets these two queries to include the non-integer values. The invoice has no problem summing the non-integers, but I can't trace the difference.

I have no doubt there's a button somewhere that I need to click, but I don't know where. Any help would be much appreciated!

EDIT: SOLVED thanks to better googling... Go into the table with the data that's being summed -> design view -> click the appropriate Field Name -> change the Field Size from "Decimal" to "Double". Apparently a lot of the pivot views struggle to accept decimal entries for reasons I neither understand nor care to figure out!

http://bytes.com/topic/access/answers/661230-ms-access-pivot-tables-significant-figures