Possible to make a DB when CSV's have no relation

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 2211 Views - Last Post: 06 August 2017 - 12:53 AM Rate Topic: -----

#1 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 10:26 PM

Hi , so basically i would like to know if its possible to make a MySQL database where CSV files can be linked to a user, if they have no relationship?
Here is an example of the CSV's I'm using (output data from monitoring units) - https://puu.sh/wRoPA/09b3146d40.png . I have tried inserting the CSV into table which works fine but still cant find a way to relate it to a user. :helpsmilie:


Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Possible to make a DB when CSV's have no relation

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13491
  • View blog
  • Posts: 53,888
  • Joined: 12-June 08

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 10:32 PM

Each user has an ID. You can add a column to that csv's table for 'user id'. Make it a key. I would also suggest just slapping a normal key on it anyways.
Was This Post Helpful? 0
  • +
  • -

#3 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 10:34 PM

I have a table created for users at the moment, but if i create another user id column for the CSV table that wont actually link the user id from the users table to that of the csv table?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 10:46 PM

Suppose you had two users, Bob and Ted. What would associate Bob with any particular line in that CSV? Why would Bob be associated rather than Ted?

If there is no relationship, nor logic, that associates users to the output data then you cannot magically connect the two things.

There must be something going on in your head that thinks the two could be connected? Explain your thought process.

The only other way to forge a relationship would be either to assign rows to users randomly, or assign them all to Bob.
Was This Post Helpful? 0
  • +
  • -

#5 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 10:54 PM

Exactly what i mean, i cant really connect them because there isn't a relation. Was just checking in case there was something i missed.. At the moment Im looking into druid which is a column based database do you think that would suit this type of data storage better?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13491
  • View blog
  • Posts: 53,888
  • Joined: 12-June 08

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 10:58 PM

Why wouldn't it link it?

Alternatively in the CSV table have an 'ID' column so you know what was imported in a group. Then have a table matching up that id to a user id.
Was This Post Helpful? 0
  • +
  • -

#7 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 11:03 PM

For an example you mean if i imported all the csv data to a table and had the id column , the id for every record in the table would remain the same int?
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 11:09 PM

View Posthokletrian, on 31 July 2017 - 05:54 AM, said:

At the moment Im looking into druid which is a column based database do you think that would suit this type of data storage better?

Do you have a need to "quickly ingest massive quantities of event data, and provide low-latency queries on top of the data"?

A million isn't a massive quantity in database terms. MySQL can handle millions.

But if you have a particular desire to explore a column based database then perhaps give it a go.
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13491
  • View blog
  • Posts: 53,888
  • Joined: 12-June 08

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 11:10 PM

Sure.. it sounds like you have many CSV imports and many users.. but one user tied to one csv import..
Was This Post Helpful? 0
  • +
  • -

#10 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 11:14 PM

View Postandrewsw, on 30 July 2017 - 11:09 PM, said:

View Posthokletrian, on 31 July 2017 - 05:54 AM, said:

At the moment Im looking into druid which is a column based database do you think that would suit this type of data storage better?

Do you have a need to "quickly ingest massive quantities of event data, and provide low-latency queries on top of the data"?

A million isn't a massive quantity in database terms. MySQL can handle millions.

But if you have a particular desire to explore a column based database then perhaps give it a go.


I would rather stick to SQL , i think i might just need to go with the solution of having the same primary key for every record in the table. :sweatdrop:

If that is the case how can i achieve that, i would obviously create the id column and other fields for the csv import. I know how to auto increment but how can i get all the id's as the same value?
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13491
  • View blog
  • Posts: 53,888
  • Joined: 12-June 08

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 11:16 PM

Easiest - get the last, max, ID.. increment by one... use that for all the inserts.
Was This Post Helpful? 0
  • +
  • -

#12 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 30 July 2017 - 11:23 PM

View Postmodi123_1, on 30 July 2017 - 11:16 PM, said:

Easiest - get the last, max, ID.. increment by one... use that for all the inserts.



Thanks! How would i go about writing that, as you can tell I'm not the most experienced person with SQL.. :notify:
Was This Post Helpful? 0
  • +
  • -

#13 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 31 July 2017 - 11:47 PM

Okay so i managed to create an id column in the CSV table and made the id for the whole column the same but doesn't work (all key values in the table are the same). Again I'm just stuck with the same issue :whatsthat: how can i assign all rows to "Bob" like andrew was saying?
Was This Post Helpful? 0
  • +
  • -

#14 ndc85430  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 590
  • View blog
  • Posts: 2,482
  • Joined: 13-June 14

Re: Possible to make a DB when CSV's have no relation

Posted 01 August 2017 - 12:42 AM

The primary key column has to contain unique values, since the primary key is used to identify a row. If you need to have multiple rows associated with items in a set of unique things (the users in your case), then the usual way to do that is with an association table. You'd have one table that contains the users (so their ID would be the primary key in that table) and then another table (the association table) that contains the user's ID and whatever data you need to store. The user ID in the association table would then be a foreign key that refers to the user table and the primary key of the association table could be a composite key.
Was This Post Helpful? 0
  • +
  • -

#15 hokletrian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 24-July 17

Re: Possible to make a DB when CSV's have no relation

Posted 01 August 2017 - 05:23 PM

I see what you mean but i feel as if i previously tried that. The whole point is basically storing csv's in the database for a company , so i can then pull the appropriate data for them to view on their own profile page. Heres an example of my db csv table:
https://puu.sh/wZbho/43cdcbdd60.png https://puu.sh/wZbho/43cdcbdd60.png

I need to basically link the whole file/table to a company/user , because pulling singular records is pointless.

This post has been edited by andrewsw: 01 August 2017 - 10:26 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2