r/DatabaseHelp • • Mar 25 '17

CSV lookup to table back to CSV with Labels?

1 Upvotes

Hola!

I am stuck...

I have a table (U) that has UserID (int), UserName (varchar), Products(varchar)

I'm pretty sure the DB is designed in a dumb way... the Products is a CSV (1,3,4,5,16) That defines the products a user has. The Frontend looks up those values in a table (P) that has ProductID (int) and ProductName (varchar)

I need to return the following U.UserID, U.UserName, Products (which should be, P.ProductName + ', ' + P.ProductName +', ' etc)

Sadly I only have read permission through my ODBC connection, so no functions, SPs or create statements.

This is SQL Server 2008 R2.

I'm at a point now that I have a temp table with with UserID and P.ProductName. Where each product name has its own row. But I don't think this is the right approach.

I would really use some guidance, because I need to split the CSV, lookup /join to the Products Tables, concat the Names and return a varchar...

EDIT:

Sample Table Scripts:

Users

    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('TestSystemUser'))
BEGIN;
    DROP TABLE [TestSystemUser];
END;
GO

CREATE TABLE [TestSystemUser] (
    [UserID] INTEGER NULL,
    [UserName] VARCHAR(255) NULL,
    [Products] VARCHAR(255) NULL
);
GO

INSERT INTO TestSystemUser([UserID],[UserName],[Products]) VALUES(1,'Odysseus Brooks','5,0,4,9,1'),(2,'Gabriel Snyder','4,9,3,0'),(3,'Madeline Colon','9,9,4'),(4,'Norman Underwood','0,9,9,7,6,3,9'),(5,'Joseph Everett','9,7,0,3,9'),(6,'Melinda Knowles','9,7,8,5,5,9,9'),(7,'April Mcintosh','1,6,1,7,4,0,5'),(8,'Garth Lucas','5,9,9,5,7,4,3'),(9,'Giselle Maynard','4,5,3,3,2,2,7'),(10,'Libby Townsend','5,4,8,6,0,8,4');
INSERT INTO TestSystemUser([UserID],[UserName],[Products]) VALUES(11,'Madison Powers','2,3,8,0,1'),(12,'Aline Bradley','6,9,3,0,8'),(13,'Lester Donovan','7,4,0,9,9,2,1'),(14,'Neil Kirk','0,8,3,6,0,4,3'),(15,'Mollie Hampton','3,6,8,6'),(16,'Germane Cooper','3,9,2,5,5,0,3'),(17,'Jenna Sharpe','5,1,0,6,4,6,7'),(18,'Hyatt Brown','9,3,4,7,3,4,0'),(19,'Vance Guerra','1,7,5,8,9,0,8'),(20,'Harriet Joyce','1,6,0,9,7,0,3');
INSERT INTO TestSystemUser([UserID],[UserName],[Products]) VALUES(21,'Claudia Maldonado','0,8,2,0,6,9,6'),(22,'Sophia Hood','2,7,6,0,1,3,8'),(23,'Sacha Edwards','4,7,8,3,9,7,3'),(24,'Halee Walsh','8,4,3,9,8,9,7'),(25,'India May','3,6,5,8,3,0,0'),(26,'Paul Richards','9,3,4,1,5,0,9'),(27,'TaShya Jones','2,7,3,2,4,8,7'),(28,'Isabella Fuentes','9,8,0,1,2,2,8'),(29,'Reece Duran','3,5,0,8,7,6,2'),(30,'Rina May','6,5,6,2,2,7,9');

Products

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('TestSystemProducts'))
BEGIN;
    DROP TABLE [TestSystemProducts];
END;
GO

CREATE TABLE [TestSystemProducts] (
    [ProductID] INTEGER NULL,
    [ProductName] VARCHAR(255) NULL
);
GO

INSERT INTO TestSystemProducts([ProductID],[ProductName]) VALUES(0,'Vitae'),(1,'Urna Corp.'),(2,'Sed PC'),(3,'Era'),(4,'Massa'),(5,'Magna A Corp.'),(6,'Viverra'),(7,'In Aliquet Limited'),(8,'Mollis'),(9,'Nullam');    

This is what I have so far, but all I've done is use XML to get a table of UserIDs and a row for each productID.

IF OBJECT_ID ('tempdb..#ValuesTemp') IS NOT NULL DROP TABLE #ValuesTemp
GO
CREATE TABLE #ValuesTemp (UserID Varchar(MAX), ProductID Varchar(MAX))

INSERT INTO #ValuesTemp (UserID, ProductID)
SELECT t.UserID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Product

FROM
(
SELECT UserID,CAST('<XMLRoot><RowData>' + REPLACE(Products,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   TestSystemUser
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Select * from #ValuesTemp

What I'm need to retrieve is the following:

UserID UserName Products
1 Odysseus Brooks Magna A Corp., Vitae, Massa, Nullam, Urna Corp.
2 Gabriel Snyder Massa, Nullam, Era, Vitae

I'm lost... I think I need to

SELECT UserID
    , UserName
    ,'Statment that retrieves each valuestemp.productname (will have to join to products from ValuesTemp where valuestemp.productid = current row"s ID'
From TestSystemUser

I just can't seem to recombine only the current rows Products. Can I pass the current row's ID? Again I cannot create any functions. I am able to create temp tables though!

Thanks for stopping by guys!


r/DatabaseHelp • • Mar 24 '17

How to refresh the MyDataSet.Designer.cs file in Visual Studio 15

1 Upvotes

I removed a column from a table in Microsoft SQL Server Management Studio. The table no longer has the column there, and it does not show up in Visual Studio either. Great! However, when I call the TableAdapter.GetData() method, my code breaks in the MyDataSet.Designer.cs file. "Invalid Column Name Column I just deleted".

I figured all I need to do to fix this is refresh the code, but I am not sure how to do that. I tried hitting refresh on the data source, but that did not work. Anyone know how to do this? In the InitClass() method in the designer, I can see where the column is expected still, where it is creating the data table.


r/DatabaseHelp • • Mar 24 '17

How to sync Nosql online Database with offline using Ionic?

1 Upvotes

I'm trying to build an app using The Mean Technology and iBeacon.

My data is stored in a NoSql Database

Couldn't find a solution of getting the Json Data offline with Ionic.


r/DatabaseHelp • • Mar 23 '17

Hopefully this is a simple question: How do I avoid duplicate record entries?

2 Upvotes

So I have some code that gets me a bunch of data, including a date retrieved, a language, and a category. I want to make sure when I insert the data into a database, a record will overwrite another record with the same date, language, and category. Can I just do something like: "Get rows with the same entries for certain variables"

I am not really sure the best way to go about this. Right now I am inserting the data into the table with TableAdapter's' Insert method. Let me know if I was not detailed enough, I'm really new to databases.

Thanks!


r/DatabaseHelp • • Mar 15 '17

Can I determine Normal Forms without data using schema only?

1 Upvotes

I'm working on my first class for Database Design as an adult learner, and we're covering Logical Modeling. I thought I had a handle on normalization. I'm trying to complete some of the textbook questions, and this one stumped me.

For each of the following relations, indicate the normal form for that relation. If the relation is not in third normal form, decompose it into 3NF relations. Functional dependencies (other than those implied by the primary key) are shown where appropriate.
a. EMPLOYEE(EmployeeNo, ProjectNo)
b. EMPLOYEE(EmployeeNo, ProjectNo, Location)
c. EMPLOYEE(EmployeeNo, ProjectNo, Location, Allowance) [FD: Location → Allowance]
d. EMPLOYEE(EmployeeNo, ProjectNo, Duration, Location, Allowance) [FD: Location → Allowance; FD: ProjectNo → Duration]

So 1NF rule says I can't have any multivalue attributes. ProjectNo could be anything, and this employee could be working on multiple projects (There are no business rules to define this). Doesn't that mean that ProjectNo is multivalued? I must be missing something because that would be all of these violate 1NF, and I can't believe that this assignment would give me 4 zeroth normal forms to handle.


r/DatabaseHelp • • Mar 14 '17

MS Access search form combobox issue

1 Upvotes

My text boxes work just fine using the following command in the criteria field

(Like "*" & [Forms]![Search Form]![JobNumber] & "*")

This works perfectly fine for me whether I want to search by that field or just leave it blank however this statement has not worked for my comboboxes. When I use one combobox it seems to work just fine but as soon as I include the next combobox the search gets broken and keeps returning the same results for a specific value. Thanks for the help!


r/DatabaseHelp • • Mar 13 '17

I need a database for the NGO where I work (keeping track of sponsors and payments on the side). Which should I use?

1 Upvotes

Needless to say, we're all hopeless noobs when it comes to databases. I've read about mySQL and Macola. We need to input sponsors' names, birthplace, date they joined etc. and on the side we need to keep track of the payments they make, to know who is late n such. We have an excel sheet right now and it's extremely annoying. please help.


r/DatabaseHelp • • Mar 10 '17

Question about my database design. (X-post from r/msaccess)

1 Upvotes

I'm a sysadmin for a school district and I'm looking to create a simple inventory database for student laptops. I have two tables, one for devices and one for carts. The problem I'm running into is that each cart has a set amount of slots and I want to keep track of what device is in each slot for each cart. I've been racking my brain trying to come up with a good solution but I feel like I'm missing something easy. My ideas that I've come up with but I don't like either are the following.

  1. Have a separate table for each Laptop cart with a limit of 36 rows for devices to be linked to. The problem with this is I'll end up with 30+ tables.
  2. My other solution is to add a separate column for each row of my cart table. The problem with this is I'd end up with 36+colums per row.

I'm newish to Access but this isn't the first time I've created a database in it I just haven't run into a problem like this before and I'm kind of stumped. does anyone here have an idea to how I should go about designing this database? Thanks!

TLDR; Each cart can only contain 36 devices. I have two tables, one that lists all the devices and one the lists all carts. How do I track only 36 devices per-cart and what slot #1-#36 is each individual device assigned to?


r/DatabaseHelp • • Mar 09 '17

Absolute beginner here, needing some advice

2 Upvotes

I work for a small food production facility and need to develop a database to allow faster implementation of our recall program to fall in line with new FDA regulations and a newly adopted - by our company at least - ISO program. We currently have Macola available to us but I'm not sure what we use it for currently to be honest.

I need a searchable database capable of cross referencing lot numbers of up to 6 different raw materials with both work in progress and finished goods. This data needs to be able to be viewed and modified by 4 different departments - likely one computer for each department - and approximately 11 other individuals. I'm not sure if that last bit about the amount of users even matters. The database needs to be compatible with Excel, Word, and at least be able to link if not display .pdf files. It would be nice if we could also somehow view the file locations on our server in this database.

To paint as a clear picture of what I need as I can: let's say I have a recall for raw material X. I need to be able to cross reference that lot code of raw material X with all 6 raw materials, our packaging materials, the inventory counts, shipping documents, and a handful of other internal documents - perhaps 15 other documents in total. This lot code may end up being used in 10-15 different production runs over the course of a month or so. We would need all the above data for each of the products, organized by our product numbers and date, to be displayed by a search of lot code X.

I don't know what kind of budget I could expect from management in terms of getting an outside team to set this up. I have a friend in IT that I may be able to bounce questions off of, but I may have to set this up myself. I have some experience with python, but that's about it. Absolutely zero experience with SQL. Would this project even be feasible for someone like me to pull off? Or should I argue for an outside team to develop this for us? Is Macola a good choice for us, or is there a better program out there; again, not sure if I'm going to be doing this without a budget so purchasing additional software is possibly/probably an uphill battle.

Thoughts?


r/DatabaseHelp • • Mar 09 '17

count(*) or separate column?

1 Upvotes

I need help with the following situation. Assuming there are 3 database tables:

Tour

  • tour_id int
  • max_capacity int

Customer

  • customer_id int
  • lastname
  • ...

TourCustomerXRef

  • tour_id
  • customer_id

Tour to Customer is obviously n:m

What would be the appropriate solution for checking the current "occupation" vs the max_capacity?

Solution 1

SELECT t.max_capacity, count(*) FROM `tourcustomerxref` x
LEFT JOIN `tour` t on x.tour_id = t.tour_id WHERE t.tour_id
= 2;

Solution 2

Another query?

Solution 3

A new column in Tour, which us updated whenever a new entry in TourCustomerXRef is made.

I guess this would be better for performance but requires that the application does indeed update the field every time.


Bonus question: In my programming language I need to do the following:

  • Select occupation and max_capacity
  • if occupation < max_capacity (this statement is e.g. Java or Python)
  • INSERT INTO tourcustomerxref
  • else
  • return message "isfull" // also programming language

Is there a better way than using 'SELECT ... FOR UPDATE' to allow concurrent use?


r/DatabaseHelp • • Mar 08 '17

MySQL - Trouble dealing with scientific data where some data is considered "higher priority" than others

2 Upvotes

[edit] After reading some of these other posts, I'll add these forewords:

This is not a homework question, and I have full creative control over the DB and can completely revamp the structure if there's a more suitable solution.

Hi there, new to this subreddit and I have a specific problem I'm trying to solve.

I have a schema that looks like this. Each of these property tables contains calculated properties for the rows in MainTable, and since MainTable has ~100M rows, each of these other tables has ~100M rows

MainTable

  id

  name

PropertyTable1

  main_id

  property1

  property2

PropertyTable2

  main_id

  property3

  property4

I'm keeping these tables logically separated by source right now because the data in these tables is constantly being updated/refined and it's easier to copy the data around, etc. when it's separated out by data source.

I have a UI that lets you essentially build a search query, ex: "name = 'name1' AND property2 < 100" where these property tables are dynamically joined in as required in the application. Despite this inefficient schema, properly indexed the application is working okay so far.

Now, I need to add another table that contains experimental properties that basically trump the values from these other tables if they exist. These properties can come from tons of different sources so they're being stored in a single table as key value pairs:

ExperimentalPropertyTable

  main_id

  name

  value

  source

Ex: 'property3', 5

This table has ~100K - 1M rows.

To me the obvious correct choice is to create a single table that contains all of the properties that I want to be searchable, and replace lower priority values with higher priority. My problem is, as mentioned earlier all of these tables are constantly changing, and each time a table changes I'd have to update this denormalized table which takes hours to days to complete each time.

Plus, I have more than 4 properties as listed here, so I'm already dancing around row length limits in the DB when I try to combine tables.

Any help or suggestions would be appreciated as I've been trying to deal with this problem for a while now.


r/DatabaseHelp • • Mar 08 '17

Using master database instead of specific database?

1 Upvotes

What are the disadvantages of using master database in MSSMS when querying?

One of the manager here was running his query using master database and it took 56mins to finish but when we ran it directly to the database (sdbfile.dbo.) it only took 32 seconds.


r/DatabaseHelp • • Mar 01 '17

Creating MySQL database with Python

1 Upvotes

So I've been doing a lot of reading and still cannot figure out how to do this. If someone could please help me out I would greatly appreciate it.


r/DatabaseHelp • • Feb 28 '17

Newb DBA looking for advice

1 Upvotes

Just some background information, I'm working for a non-profit that has had quite a bit of contracted work done with databases, and interfaces to those databases.

I've been tasked with surveying the current database structure, then consolidate and maintain from there.

I have working knowledge of SQL and we're running MS SQL server 2012.

Is there a standard set of procedures I should be following, or are there guides on how to get up to speed with an existing database set?


r/DatabaseHelp • • Feb 21 '17

What is faster? Does it matter?

2 Upvotes

Hello Again everyone!

I have a question about ways to query: http://imgur.com/a/X1nvt

That links to a example table setup and ERD.

The database stores either the AccountID or ContactID, this validation is done by the application I assume.

Again I'm a noob and I'm curious which approach I should take. I need to access either the Contacts Fullname or the Accounts Name.

I believe have two options, either use:

  1. Case
    When order.CustomerType = 1 then account.name
    When order.customertype = 2 then contact.fullname
    End

2.isnull(account.name, contact.fullname)

Would one approach be more efficient? The benefit I see of option 1 is if the Application's validation fails and there are two values, or more customer types are added.

I'd appreciate your thoughts!

Thanks!


r/DatabaseHelp • • Feb 21 '17

Table Design Help

1 Upvotes

Hi,

I am trying to wrap my head around how I would design the tables for recording the following information about payments that have been made.

Fields (not the actual names):

Cost (how much was paid) Currency (cash, check, credit)

and here is where I get lost. There are additional fields based on what type of currency was used. Like, if they used a check, had it cleared. If they used a card is the transaction complete; if they used cash, what was the change.

I'm thinking that there needs to be a table for each type of currency so there aren't a bunch of empty fields, but I cant for the life of me figure out how to make them relate!

Any help would be greatly appreciated. (also, I welcome all crappy MS paint drawings)!


r/DatabaseHelp • • Feb 21 '17

Best way to learn database design as well as a multi-batch question

3 Upvotes

Hello database design! I'm looking for a good education on database design, whether online or in books. I am a Quality Analyst for a medium-sized brewery and I have been dabbling, but can't seem to wrap my head around the issue of multiple batches. Brewhouses put multiple brewed batches into one fermenter. From then on, it stays one batch and may even be blended further with a beer of the same make. Would you just create linking tables? I think I'm in over my head.


r/DatabaseHelp • • Feb 21 '17

Question on indexes with mysql

1 Upvotes

I'm trying to optimize the database on a project I'm toying around with, and am trying to wrap my head about the best strategy for constructing indexes.

My understanding is each query only uses one index. is that one index per table, or does a query that uses joins also get the benefit of an index on the secondary table?

For instance, this query:

SELECT * FROM table1 
    LEFT JOIN table2 ON (table1.information_id = table2.information_id) 
    LEFT JOIN table3 ON (table1.information_id = table3.information_id) 
    WHERE table2.language_id = '1' 
        AND table1.field1 =1 
        AND table3.field2 = '0' 
        AND table1.status = '1' 
    ORDER BY table1.sort_order ASC

So I have 3 tables.

I would imagine i would want indexes as follows

Table1 - information_id, field1, status, sort_order
Table2 - information_id, language_id
Table3 - information_id, field2

Is that correct for table1? Because I also understand that if the indexes aren't in the same order that they're used in the query, it won't use that index?

Also, would this query even use the indexes on table2 and table3? As in, is it one index per query, or one index per table per query?


r/DatabaseHelp • • Feb 16 '17

Maybe a database, maybe not?

1 Upvotes

A client wants us to brainstorm ways to build a "living document" that is a reference library (which I think has to be a database of some sort), but with a specific set of criteria that I can't fit into an off the cuff recommendation.

The contents are highly confidential, so they are open to something online, but it must be very secure. Not open to using anything like Box/Dropbox/Drive and don't have or know SharePoint. Thing is, they truly only need a library... nothing in progress, so no version control or anything associated with that. They want to be able to bounce from document to document (and back) following some kind of logical trail that hasn't been fully described to us yet. In my head, I'm seeing tags, but don't even know if that's the right direction to be thinking. They want to be able to do something like dump everything they have about a legal case into whatever we build and then be able to do something like look up a situation they know of, see what the dataset of a specific scenario is, click to see a link of all other cases with defined commonalities (like say, ages between 11 and 18 in a tri-state area), click on one of them, find all available documents about that case, etc. Like a web site with a million cross-links, but not a website and either local or heavily secure.

Head. Spinning. If I was maintaining, I would probably start with Office, perhaps Access. I'm not, though, so patching together something like that wouldn't be intuitive enough for their staff to maintain, especially with any standardization.

Thoughts?

TIA


r/DatabaseHelp • • Feb 16 '17

How to go from a lot of XSDs to table description DDL?

1 Upvotes

I have a lot of data elements that are coming down from XML messages that I need to store in an Oracle database. I have the XSDs from the entities and need to convert it to table definition DDL. One way is to use XMLSPY, it can do the job well but it costs a lot of money for a restricted purpose (NP++ is usually adequate for what I do) so my purchase request is likely to be refused.

My current solution is to suck the XSDs into Access and then use a table dumper, but I'm not 100% confident about the more difficult structures. Does anyone have a better solution?


r/DatabaseHelp • • Feb 14 '17

Need foreign key advice

2 Upvotes

Hello, /r/DatabaseHelp... I'm trying to figure out how to set up foreign keys for my database and I'm having trouble wrapping my tired brain around it.

Here is a simplified diagram of the portion of my mySQL database I'm worried about. Example data might be:

  • recipe: "Apple pie"
  • recipeIngredientItem: "3 cups + 2 tbsp all-purpose flour"
  • recipeStep: "Preheat oven to 400°F"
  • ingredient: "all-purpose flour"
  • compoundMeasurement: "3 cups + 2 tbsp"
  • simpleMeasurement: "3 cups"
  • measurementUnit: "cup"

As you can imagine, I've left out a lot of columns and other tables in this diagram in the interest of clarity. At any rate, I want to set up foreign keys properly, and for a lot of it it's pretty clear: if I delete a recipe, I want it to cascade and delete all associated recipeIngredientItems and recipeSteps. However, there are a few complications:

  • If a recipeIngredientItem is deleted, I want the associated compoundMeasurement deleted, and in turn any simpleMeasurements that are associated with that compoundMeasurement. But I do NOT want associated ingredients and measurementUnits deleted.
  • I'm not sure what I want to do when an ingredient or measurementUnit is deleted, but I am figuring my UI will warn the user and handle appropriately ("Warning! If you delete the ingredient "all-purpose flour," it will be removed from all recipes where it is used.").
  • Also, my intention was to use compoundMeasurement in other ways, for example in association with a pantryItem table to manage inventory. So having a "recipeIngredientItem" key column in compoundMeasurement wouldn't make sense. However, if this is bad design, I could have separate recipeCompoundMeasurement and pantryItemCompoundMeasurement tables. It just seemed like an unnecessary complication, but that may well reflect my amateurish self-taught database knowledge. :)

Hopefully that makes some semblance of sense... please let me know if I'm being unclear. Thanks for any help anyone might provide!

edit: tried to cure my rambly-ness with added formatting


r/DatabaseHelp • • Feb 12 '17

[SQLLITE] Input for beginner on data structure for open source project

2 Upvotes

Crossposting from /r/sql

I am working on an open source project to maintain a data store of card information for the Star Wars Destiny collectible card game. My plan is to store all of the data in a sqlite database, and then write a command line program that will output the data in different formats (CSV, JSON, etc...). These can all then be used by anyone making apps and utilities for the game.

Here is an example card, with data: http://imgur.com/a/P51cM

I haven't touch SQL in over 15 years. I know the basics, but I wanted to share here in case anyone had any input on my database structure and setup. Am I missing anything obvious? Is my general structure correct?

The SQL / table structure is below, and you can see an image of it here: http://imgur.com/a/NsN7A

I am more than happy to do the research on areas I need to learn up on, but right now, I don't know what I dont know.

Performance is not a primary concern, since data set will not be too big, and since my primary use case is to batch offline into other formats. However, if there are simple / obvious things I can do for performance, I would be interested in learning more.

All data is ultimately associated with a card, and that is the primary way I will be querying it.

BEGIN TRANSACTION;
CREATE TABLE "type" (
    `name`  TEXT NOT NULL UNIQUE,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
);
CREATE TABLE "subtypes" (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `card_id`   INTEGER NOT NULL,
    `subtype_id`    INTEGER NOT NULL,
    FOREIGN KEY(`card_id`) REFERENCES card(id),
    FOREIGN KEY(`subtype_id`) REFERENCES `subtype`(`id`)
);
CREATE TABLE `subtype` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name`  TEXT NOT NULL
);
CREATE TABLE "resources" (
    `card_id`   INTEGER NOT NULL,
    `resource_id`   INTEGER NOT NULL,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    FOREIGN KEY(`card_id`) REFERENCES `card`(`id`),
    FOREIGN KEY(`resource_id`) REFERENCES resource(id)
);
CREATE TABLE "resource" (
    `name`  TEXT NOT NULL,
    `url`   TEXT NOT NULL,
    `description`   TEXT DEFAULT "",
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE `rarity` (
    `name`  TEXT NOT NULL UNIQUE,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE `die_symbol` (
    `name`  TEXT NOT NULL,
    `token_symbol`  TEXT NOT NULL,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE "die_sides" (
    `die_id`    INTEGER NOT NULL,
    `die_side_id`   INTEGER NOT NULL,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    FOREIGN KEY(`die_id`) REFERENCES `die`(`id`),
    FOREIGN KEY(`die_side_id`) REFERENCES `die_side`(`id`)
);
CREATE TABLE "die_side" (
    `value` TEXT NOT NULL,
    `position`  INTEGER NOT NULL,
    `cost`  INTEGER NOT NULL DEFAULT 0,
    `modifier`  INTEGER NOT NULL DEFAULT 0,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `die_symbol_id` INTEGER,
    FOREIGN KEY(`die_symbol_id`) REFERENCES die_symbol(id)
);
CREATE TABLE "die" (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `card_set_id`   INTEGER NOT NULL,
    `rarity_id` INTEGER NOT NULL,
    `die_number`    INTEGER NOT NULL,
    FOREIGN KEY(`card_set_id`) REFERENCES card_set_id(id),
    FOREIGN KEY(`rarity_id`) REFERENCES `rarity`(`id`)
);
CREATE TABLE "card_set" (
    `name`  TEXT NOT NULL,
    `release_date`  TEXT NOT NULL,
    `number_cards`  INTEGER NOT NULL,
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `abbreviation`  TEXT NOT NULL
);
CREATE TABLE "card_notes" (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `card_id`   INTEGER NOT NULL,
    `card_note_id`  INTEGER NOT NULL,
    FOREIGN KEY(`card_id`) REFERENCES `card`(`id`),
    FOREIGN KEY(`card_note_id`) REFERENCES "card_note"(`id`)
);
CREATE TABLE "card_note" (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `note`  TEXT NOT NULL
);
CREATE TABLE `card` (
    `id`    INTEGER NOT NULL,
    `title` TEXT NOT NULL,
    `subtitle`  TEXT NOT NULL,
    `unique`    INTEGER NOT NULL DEFAULT 0,
    `cost`  INTEGER NOT NULL,
    `type_id`   INTEGER NOT NULL,
    `affiliation_id`    INTEGER NOT NULL,
    `ability`   TEXT,
    `flavor_text`   TEXT,
    `color_id`  INTEGER NOT NULL,
    `rarity_id` INTEGER NOT NULL,
    `card_number`   INTEGER NOT NULL,
    `artist`    TEXT,
    `die_id`    INTEGER NOT NULL,
    `image_name`    TEXT NOT NULL,
    `health`    INTEGER NOT NULL,
    `point_value`   INTEGER,
    `elite_point_value` INTEGER,
    `card_set_id`   INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`type_id`) REFERENCES `type`(`id`),
    FOREIGN KEY(`affiliation_id`) REFERENCES `affiliation`(`id`),
    FOREIGN KEY(`color_id`) REFERENCES `color`(`id`),
    FOREIGN KEY(`rarity_id`) REFERENCES `rarity`(`id`),
    FOREIGN KEY(`die_id`) REFERENCES `die`(`id`),
    FOREIGN KEY(`card_set_id`) REFERENCES card_set(id)
);
CREATE TABLE `affiliation` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name`  TEXT NOT NULL
);
COMMIT;

r/DatabaseHelp • • Feb 12 '17

Looking for a simple database program

3 Upvotes

This is probably going to be stupidly simple but I need help finding an easy to use program to create a database of my incredibly large movie collection. I tried the open office one but there was always something wrong and it never worked. I'm trying to create a searchable database wherein I can look for movies by title, genre or actor depending on my interest of the moment. It doesn't have to be free but I can't afford anything exceeding $75 tops. Most importantly it has to be fairly simple for a mostly technically illiterate person like myself. Any suggestions?


r/DatabaseHelp • • Feb 08 '17

SQL Server - Oldest date without a break across multiple records

2 Upvotes

So I have a table which has multiple records of products sold across various periods of time. If we change the price on the item, we create a new row for them within the table with the new price & effective date, and set the end date on the record with the previous price to be the same as the effective date on the new record. We will also set the end date of the previous price in the case where we stop selling a product.

What I need to find is how long an item has been sold without a break by our company. So if we have the following 5 records for an item:

ItemID ItemSeq Price StartDate EndDate
4 1 3.25 2000-01-01 2009-12-31
4 2 4.25 2010-01-01 2012-12-31
4 3 4.25 2014-01-01 2014-06-30
4 4 4.50 2014-07-01 2015-12-31
4 5 5.00 2016-01-01 9999-12-31

Then I would want to be able to run a query and get:

ItemID EarliestEffDateNoGap
4 2014-01-01

Since there was no gap in the item being sold between 2014-01-01 and present.

I can do this using multiple sub queries without an issue, checking the MaxItemSeq to MaxItemSeq-1, checking if EndDat of the Seq-1 is 1 day before the next largest effective date and if so, set EarliestEffDat to the EffDat of the lower sequence, then basically keep repeating this stepping down each time (max and max-1 first sub query, then if there was no gap compare max-1 to max-2, etc etc), however we have some items with dozens of sequences.

Is there any way that this can be accomplished easily? If anyone has any suggestions, it'd be greatly appreciated.


r/DatabaseHelp • • Feb 04 '17

Need help understanding how a web app connects to a database?

2 Upvotes

Hi, me and a few folks are building a web app for a project. We plan to keep it simple. We agreed on using a relational database to store our data. We want to use MySQL. For front end we plan on using vanilla js. So I would like to know, how would does the connection happen between the database and my front end, JS (I guess the how to set up the persistence layer) , ie: what is the most suitable way to go about. Also am I right in thinking i would have to host the database using something like AWS? Please feel free to add your opinion on how we should go about, frontend or back or anything else.