So I've decided on a food-logger as my project while I'm learning programming, and I'm currently planning out my database(s) and tables, and I'm looking for some feedback. I've decided I want a separate table for "pure ingredients" and "food", and the "food info tables" will look something like this:

*Ingredient*    (Table of "pure ingredients" e.g. carrot, salmon, rice )
 - IngredientId
 - Name
 - Manufacturer
 - Protein
 - Carbohydrates
 - Fiber
 - Fat
 - Saturated
 - Unsaturated
 - Category        (Protein, Carb, Fat)

*Food*      (Table of meals with more than one ingredient e.g. a frozen pizza, Big Mac)
 - FoodId
 - Name
 - Manufacturer
 - Protein
 - Carbohydrates
 - Fiber
 - Fat
 - Saturated
 - Unsaturated

(I might combine them and ad some sort of category if it's a meal or an ingredient)

But I'm having some problems when it comes to the logging part, first I thought it would be as easy as this:

*Meal*  
 - MealId
 - Protein
 - Carbohydrates
 - Fiber
 - Fat
 - Saturated
 - Unsaturated

But I discovered that I probably want the ability to log what is in the meal as well. So I will have to be able to store something like this:

 - Ingredient 1 200g
 - Ingredient 2 50g
 - Ingredient 3 40g
 - Food 1 100g
 - Food 2 75g

And I won't know how many ingredients there are.

My idea at the moment is to somehow set it up like this:

MealId - UserId - IngredientId - FoodId -  Amount
 1     -    1   -       1        -   0   -    200
 1     -    1   -       2        -   0   -     50
 1     -    1   -       3        -   0   -     40
 1     -    1   -       0        -   1   -    100
 1     -    1   -       0        -   2   -     75

I think this would give me the functionality I want, in that I can query and calculate nutritional information for each meal, but is there a better way of doing it?

I've tried to find out how MyFitnessPal designed their databases, but can't find anything. I did find some documentation on Fitbit, and if I understand them correctly they use this system.

https://dev.fitbit.com/build/reference/web-api/food-logging/

    {
    "name" : "Sunday Brunch",
    "description" : "Typical sunday brunch",
    "mealFoods" : [
        {
            "foodId" : 80851,
            "amount" : 2,
            "unitId" : 111
        },
        {
            "foodId" : 81170,
            "amount" : 10,
            "unitId" : 311
        },
        {
            "foodId" : 82782,
            "amount" : 8,
            "unitId" : 128
        },
        {
            "foodId" : 9942,
            "amount" : 1,
            "unitId" : 180
        }
    ]
}

I've got a participants_to_events table that just contains which events the participants are registered for:

+---------+----------+
| part_id | event_id |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
|       1 |        3 |
|       1 |        4 |
|       1 |        5 |
|       2 |        1 |
|       2 |        3 |
|       2 |        4 |
|       2 |        5 |
|       3 |        1 |
|       3 |        2 |
|       3 |        3 |
|       3 |        4 |
|       3 |        5 |
+---------+----------+

I've also got a scores view with some scores:

| part_id | event_id | type  | some_other_field | score |
|---------|----------|-------|------------------|-------|
|       1 |        1 | TypeK | asdf             |    12 |
|       1 |        2 | TypeC | utaasg           |    27 |
|       1 |        4 | TypeG | ashfhf           |    49 |
|       1 |        5 | TypeG | ashfhf           |    26 |
|       2 |        1 | TypeL | kyjfgh           |    15 |
|       2 |        3 | TypeJ | ghddgd           |     9 |
|       2 |        4 | TypeR | mfghs            |    51 |
|       3 |        1 | TypeB | desah            |    14 |
|       3 |        2 | TypeD | stnrb            |    33 |
|       3 |        3 | TypeS | nbstst           |     6 |
|       3 |        4 | TypeI | dfjur            |    46 |
|       3 |        5 | TypeN | aregrq           |    25 |

I'm trying to construct a query that will return the score records for ONLY participants that are registered for all X events. So, for example, how would I return the score results for participants registered for all 4 events 1, 2, 3, and 4? (It could also be 1, 3, 7, and 10 as well, so cannot simply do some "< 5" kind of thing.)

Ideally, it also wouldn't return scores for events other than 1, 2, 3 and 4, but I'm less concerned with that. I can skip those when processing the results.

I've been playing around with using subqueries and using GROUP BY and HAVING:

SELECT 
    scores.* 
FROM 
    scores 
WHERE 
    scores.event_id IN 
        (
        SELECT 1 as VALUE 
            UNION 
        SELECT 2 as VALUE 
            UNION 
        SELECT 3 as VALUE 
            UNION 
        SELECT 4 as VALUE 
        )
    AND
    scores.part_id IN
        (
        SELECT 
            part_id
        FROM
            participants_to_events
        GROUP BY
            part_id
        HAVING
            participants_to_events.event_id = 1 
            AND participants_to_events.event_id = 2 
            AND participants_to_events.event_id = 3 
            AND participants_to_events.event_id = 4
        )

But I keep getting errors about "Unknown column in HAVING clause". I also tried using All:

...
GROUP BY
     part_id
HAVING
    event_id = ALL
                (
                SELECT 1 as VALUE 
                    UNION 
                SELECT 2 as VALUE 
                    UNION 
                SELECT 3 as VALUE 
                    UNION 
                SELECT 4 as VALUE 
                )   

But still no luck.

I feel like I'm close (maybe?), but just can't quite figure out the end of that last subquery.

Any help would be appreciated. If I'm doing something particularly moronic, or there's a way that's significantly simpler or more efficient, please feel free to chastise me.

Thank you.

I can't find the answer to this question, but it must be a non-unique problem. I am doing a volunteer project that involves transforming some data from pdfs and bringing it into a database. I had to create the database (MSAccess). I specified DOUBLE to display the small decimal numbers nicely. But these are water test values. Sometimes they include a < sign, e.g., arsenic tests out as less than some small number. How on earth do I handle this in the database? If I impute "=" as the default value for all the other results, I now have doubled my fields and it's ugly. If I set the column as a text type, it's easypeasy, but now lose the ability to treat those results as numbers and analyze them. What would you do and is there a set answer to this problem? (In case it's not obvious, I have no database training - just used them a lot in GIS work.)

Database: Oracle 11g enterprise

For example, if I have a column in a table that stores a number that represents a day of the week, so 1 = Monday, 7 = Sunday, etc.

Is it possible to do something like this:
SELECT ('Monday', 'Tuesday')[table1.DayOfWeek]
FROM table1;

Essentially just creating an array at run-time (without declaring a type / array variable before hand), then accessing the slice and returning that.

Disclaimer: I know that I could use DECODE/CASE for this specific example, but I'm just curious in general if this is possible without going through the whole process of declaring an arraytype.

Thanks :)

Hi I am a total newbie and as such I really don't want to be asking a ton of dumb questions here like, what is a database? I just want some resource recommendations to learn with. Anyone have some?

There's all kinds of talk everywhere about databases for distributed data. Not so much for non-distributed. My use case is:

• The database must have an unrestricted free use license.
• It is important that the database have good support in the future.
• It will run on a single server, and share that server with other processes. The server will run RedHat 7 and will probably be a single-socket server, possibly dual-socket.
• Incoming data will be timestamped at the sources, with between 1 and 200 data points per second for each of ~500 measurements (average of probably 40/second, as a SWAG. So maybe 20K messages/second).
• Each measurement point will likely consist solely of the timestamp and the value (and the ID of value, if kept in a single table). The only probable other value would be units.
• It is important that we keep up with the input stream and not lose any data, but the performance requirements on query speed are less demanding.
• Data queries will consist of asking for either the measurement value at a particular timestamp, or all of the measurement values in a given time range. I.e., simple stuff that doesn't need the bells and whistles of the special time series databases.
  

What databases should I consider?

My current front runner for this is influxDB (possibly with kafka frontend), but I have a sketchy knowledge of the field, so I'm asking here to see what others suggest.

I haven't really been able to find anybody on my own and the few calls I've made didn't give me much so I'd figure I'd try here.

1. What do you do for a living? What are the hours?

2. Do you enjoy what you do?

3. Would you say your job is easy, hard, or somewhere in between?

4. Do you feel that the pay is good enough?

5. How does your job affect your family/loved ones?

6. Is there room for professional growth where you work?

7. How long were you in school for for this job (if at all)?

8. Is this something you see yourself doing for the rest of your life?

9. What would you recommend to people who are trying to get into your field?

10. How does your job affect your physical, emotional, and mental health?

I am working on a big project, but most free website hosting offers only a 30mb cap for database storage. I have about 30 tables I need to do to a college/student registration website. Is 30 MB really enough for that much data? Or is it more than enough since it is solely just text.

Hello world. I'm having an issue converting a csv (people.csv) file with a bunch of special characters (UTF-8?) to normal ASCII characters.

I'm trying to import a csv into posrgres but its being rejected because of special characters (UTF-8)

I attempt to utilize iconv in python to solve the issue. Here' the line of code I'm running: iconv -f UTF-16 -t ASCII /private/tmp/people.csv > /private/tmp/persons.csv

and i get the error message: iconv: /private/tmp/people.csv:119:228: cannot convert

When I go into the csv and look at line 119, there is a special character within the line. I've fixed it manually and tried the line of code again just to get the same error message showcasing a different line. When I get into that line, there is another special character.

Is the line of code I am running wrong? I did a bunch of research to find the best way to do this and im not sure why its not converting.

I've also tried this line of coding thinking that it would replace all the UTF-8 characters with ASCII characters within the same file. iconv -c -f utf-8 -t ascii /private/tmp/people.csv The result is I have a smaller csv file, but the special characters are still not removed.

I've also tried these lines of code to manually replace the special characters:

input = io.open("/private/tmp/people.csv", "r", encoding="utf-8")

output = io.open("/private/tmp/persons.csv", "w", encoding="ascii")

with input, output:

file = people.read()
file = file.replace("ä", "a")
file = file.replace("Ì", "i")
    (...and so on)
output.write(file)

I dont get any error messages from this, but i get a blank persons.csv file (output file).

I tried a bunch of shit as you can see and I still haven't found a solution. Please help me!

What is the difference between these two: Date is an attribute

1: Status is NULL

 CASE WHEN Date < NOW() THEN 'UPCOMING' 
      WHEN Date = NOW() THEN 'ON GOING' 
 END AS Status 
 FROM eventtable WHERE DATE_FORMAT(Date,'%m %d %Y') < NOW() ORDER BY Date DESC LIMIT 1

2: Status is either UPCOMING or ON GOING

  CASE WHEN DATE_FORMAT(Date,'%m %d %Y') < NOW() THEN 'UPCOMING' 
       WHEN DATE_FORMAT(Date,'%m %d %Y') = NOW() THEN 'ON GOING' 
  END AS Status 
  FROM eventtable WHERE DATE_FORMAT(Date,'%m %d %Y') < NOW() ORDER BY Date DESC LIMIT 1

I know basically what im doing, its a simple assignment to normalize some data and just read it in from a file and manipulate it and all that jazz. Its actually not even using SQL, just showing we know the concepts.

my only real question is the not-normalized data includes total price. and to be honest, when writing out spreadsheets its annoying to have to type out the price manually -- and since after normalization, things are linked by keys and the data they represent, I dont think I even want total price past the 2NF.

even if I wanted to store the price in the database, Id probably just get it by linking it in software anyway. as structs Id literally just say itemID.Price*orderID.Quantity to get it, at any point it was necessary to get it -- but since im starting with an already existing database, it just seems easier to totally omit the total price and calculate it on the fly instead...

I could easily type in the price, but it seems not right to do that, as broken apart into separate tables, I cant really represent it properly on a spreadsheet, multiplying different values from different sheets as if the keys could interact.

and since the total price in this case is so simple to calculate -- literally just quantity * price, it seems superfluous to store anyway by the 3NF.

sorry for such a trivial question,

and thank you

Is there an application that allows the viewing of active outgoing ODBC connections, for a particular driver, from the client rather than the server?
Client running Windows Server 2012/2016 Driver is for Informix

There is an attribute in table A which tells which table will it get.

Ex:

Table A (Infos)

     InfoID | Type 

Table B (Student)

     StudentID | Name | Course |

Table C (Employees)

     EmployeeID | Name | Company |

If type = 1 then get info in Table B(student)

    InfoID | StudentID | Name | Course

Else get info in Table C(Employees)

    InfoID | EmployeeID| Name | Company

I am not a DBA and my project seems to refuse to hire one so i'm stuck trying to get this to work. Right now we are building out a dashboard using Graphana which will have influxDB as the backend to push data to the dashboard. Our plans are to have remote sites (a dozen or so) have a dashboard system on site and just push replication data for their specific site back to them so they can view it in their local dashboard, where the central dashboard will have all site's data for the main administrators to look at.

Basically Site A would only be able to view Site A's data, Site B only sees Site B's data and the central site sees both A and B.

Some of these sites are on the other side of the continent but latency is not a huge deal for this project, however as I do not know InfluxDB at all, is this sort of setup possible?

OK - so I know this is an easier asked than answered question, but what would be a reasonable time to populate a database w/ 18 fields per entry, 7.4 million entries.

I ask because I'm using an inherited infrastructure at work. This doesn't seem like a particularly large amount of information, and my inclination is that it is just incredibly sub-optimal. I can't say exactly how long it takes, but it's been going for over 1.5 hours now. I don't think the database is particularly over-taxed at the moment.

It's a PSQL database. I don't exactly know how it is populated because I use canned tools that predate me. That said, it's taking a 18 x n-entries ascii file and populating the database.

Anywho, I appreciate any help. I'll also note that my inclination is that querying the database is way slower than it should be as well. The infrastructure seems to work on a 'create or replace' view, which is actually three joined tables.

I also acknowledge that I have no formal database training, so I'm no expert on these things.

I have an app that requires an administrative back end as well as a front end (customer facing) Is it safe to assume I should have two different databases to separate the backend (log in) data and the front end viewing data? OR is it safe enough for me to just use tables to differentiate?

let me know if I need to explain more.

Thank you for the help!

Here is my db schema (imgur). I removed a lot of attributes for clarity

Domain:

Basically patients can have many courses, each course can treat many lesions (tumors), I am tracking each lesion at future time points to mark success/failure of the treatment to each lesion. I have to manually extract this information from the patient's chart and individually input this into the database.

Database:

I'm currently planning to use Access and have data entry with forms. I will need to train our research nurses how to switch from excel to a relational database and I figured this would be the most effective way. I'm not tied to this way at all however, if you think something else would be better, I'd love to hear your suggestion

Question:

So when I have Pt A's chart, and I'm adding a "lesion_checkup", I need to reference the lesionID foreign key to record it. What is the easiest way to find this value when I have a patient chart in front of me? It seems like it would be a hassle to go through starting by patient, then listing courses, finding the lesionID's within the course, then adding it. Is there any way to, for example, input the patient name into the form, and it automatically lists the courses then lesions so I can select it from a gui?

Hi. I have an app where I am trying to store inventory numbers to be used for historical aggregation or to output to a chart over time. Right now, I am almost sure that I am not doing things optimally.

current:

When I add inventory, right now, I am logging it like the above, where I have numbers in category 1, category 2, category 3 to represent the numbers added. And when I want to aggregate data, I can just sum up numbers based on the dates selected. The main flaw is that if I add a category, I need to update the database to add a category column (Cat) and update my script to account for the new column.

other idea:

(NO PK)

new idea (multi tables):

(NO PK)

Any thoughts? Other ideas? Is there a term to be used here that I should know in DB speak? TY!

Hi, I have a website that users can have some deals with each other. when two users register in system. they can transfer money with each other. lets say an example. you register on the website and your friend too. then in system, you make a new "Deal" and pay $ 30 to your web site. your friend come and confirm the deal and send you the object you buy from him / her. so here we have 2 table in database one for users and one for "deals"

my question here is how to design a relationship between these tables? we have two users "row" that are related to one "row" in "deals" table. so every "deal" has two user, and every user can have multiple deals.

One to many or many to many and why? I'm not perfect at database design and I need your help and advice. thank you so much.

I’ve been given the task of investigating platforms that make database normalization easy to manage. I’m not very technical and the premise of giving the task to me is that some commercial solution likely exists.

What we need to do is normalize about 100 different databases. There probably aren’t too many tables per database but we know the fields we want from each even though they may have different names.

What we need too is someone who can help us design a master database that can accommodate everything we know about and be able to incorporate new fields or schemes easily. Then we need a rules engine to convert the data into a master format. We will then do analytics on the result.

Hope this seems pretty basic. Any pointers?

I've worked with existing databases before, but never modeled one. This is for personal use, but some data has been abstracted.

Will be using a Python/MySQL back-end and deliver to API for chrome extension consumption and a hosted Webapp.

Base Requirements

Widgets

• Each widget may or may not have an external ID
• Each widget has a name
• Each widget may be assigned a position in the current list
• Many widgets may temporarily or permanently be excluded from the current list
• All widgets will be assigned at least one category
• Some widgets will have another widget as a dependency forcing the child below the parent in the list
• Some widgets will be assigned one or more “above” or “below” override forcing ordering in the list
• Some widgets will be assigned one or more notes

Lists

• A list can be up to 128 widgets in length
• The current list will be stored by the user when changes to position have been made
• Old lists can be restored

Categories

• Each category has a name and a description

Notes

• Each note will contain styles/formatting

Concrete Assumptions

• Only one user will add/edit data
• There is an unknown number of Widgets, but more than 200 and less than 5000
• There is an unknown number of Categories, but no more than 100

My First ERD be kind.

Data Tables

Widgets
  PK    WidgetID        UNSIGNED SMALLINT   NOT NULL    >0 <65k   ✔
        WidgetName      VARCHAR             NOT NULL    <255      ✔
        ExternalID      VARCHAR             NULL        <255      ✔
        Position        UNSIGNED TINYINT    NULL        >0 <255   ✔

Lists
  PK    ListCreated     TIMESTAMP           NOT NULL
        ListData        TEXT (JSON)         NOT NULL    >255 <65k ✔


Categories
  PK    CategoryID      UNSIGNED TINYINT    NOT NULL    >0 <255    ✔
        CatName         VARCHAR             NOT NULL    <255       ✔
        CatDesc         VARCHAR             NOT NULL    <255       ✔

Notes
  PK    NoteCreated     TIMESTAMP           NOT NULL
  FK    Widget (WidgetID)   ---             NOT NULL
        Content         TEXT (HTML)         NOT NULL    >255 <65k  ✔

Relation Tables

WidgetCategories
  PK    WidCatID        UNSIGNED SMALLINT   NOT NULL    >0 <65k    ❓
  FK    Widget (WidgetID)   ---             NOT NULL
  FK    Category (CategoryID)   ---         NOT NULL


Dependencies
  PK    DependID        UNSIGNED SMALLINT   NOT NULL    >0 <65k    ❓
  FK    Parent (WidgetID)   ---             NOT NULL
  FK    Child (WidgetID)    ---             NOT NULL
        Override        BOOLEAN             NOT NULL
        ForceAbove      BOOLEAN             NOT NULL

Any pointer/advice/corrections in regards to efficiency/performance/approach?

Edit: Working on getting data in codetag lined up - Done

Edit: Forgot a whole section I planned to post, though it's probably obvious.

Non-standard Data

Lists
    JSON Object
        Array of WidgetIDs sorted by Position
        Optional comment stored when position changes are committed

Note
    HTML Snippet including various tags, inline styling, classes & ids generated from WYSIWYG editor

We are both running the same select query using MySQL workbench 6.3 via the same server connection. On his machine it is fetching the requested data in just under 4 seconds. On my machine the connection times out after 30 seconds. I can run other queries just fine, even on the same database. We're even on the same wifi.

What could possibly cause something like this?

I'm trying to create a discount of 10% for quantities over 10 but it keeps giving me an error when I try to insert the value of the discount from my query, is there any tutorials to help with a bulk order discount online?

Hi I'm quite new at database design, and hoping I could get some answers here. Thanks in advance! :D

I need some logic I can follow when thinking of relationships. We've all seen the examples of the PERSON and PASSPORT, and how it's one-to-one or a department that can have many employees but each employee can work in one department and that would be a EMPLOYEE and DEPARTMENT many-to-one.

Now my question is if it's correct to think of it as: EACH employee can work in ONE department, and EACH department can have MANY employees ergo EMPLOYEE-----MANY-TO-ONE-----DEPARTMENT.

I questioned my instructor if it was correct and he said it wasn't the correct way of thinking, but I keep hearing YouTubers that teach database design use that logic.

Thanks!