Separating fields into multiple tables using SQL.?

Question

So I'm part of a research program over summer that's trying to work with some data from delicious.com. I have an SQL database that lists encrypted user names, a bookmark (url) the user has saved to delicious, and the tags they used to describe that bookmark. I need to find a way of separating each user into their own table with their associated bookmarks. The tricky thing is, if a bookmark has more than one tag, the database has a line for each tag. So a user's bookmark with the tags "news", "finance", and "sports" would be separated into 3 lines like so:

"User1" "yahoo.com" "news"
"User1" "yahoo.com" "finance"
"User1" "yahoo.com" "sports"

I'm thinking the first step into tackling this is to give each user a numerical ID so that each table can be given the user's ID# to easily iterate through all the tables at a later point (table++). I'm very new at SQL but am learning quickly. I've already used Java to connect to a MySQL database if a second language is needed to handle my problem (I'm actually more adept with C++). Any tips on this would be greatly appreciated.

Answer 1

I'm not a huge SQL guy so other people that write queries prob can tell you the exact SQL to use without using an app at all.



Since I'm more adept at handling the data within my application I'd pull all the data into a dataset. I'd then have X amount of tables with untyped data. I'd create a new table within my dataset and loop through the others to get a huge table parsing my data into whatever fields I want. You most certainly could also do the same in 1 table without parsing.



Anyway once I have access to my huge table I can manipulate the data anyway I choose by running a loop on the data.

Answer 2

I'm not sure it's a good idea to create a separate table for *every* user. It makes for cumbersome SQL since with that design, you'll always want the table name to change but the SELECT and WHERE clauses in SQL to stay the same. SQL is better suited to WHERE clause criteria always changing, but table names in the FROM clause that don't change. Step back and look at Relational Modeling where you would specify generic entities like: USER, BOOKMARK, TAG which have specific attributes of the generic entities like: "User1", "yahoo.com", and "news". The generic entities become your tables in your model, then you normalize. (An analogy to Object Oriented Programming is that tables are "Classes" and the rows are instances of the Class, i.e. "Objects". You wouldn't create a separate class for each user: User1.cpp, User2.cpp, etc. - you just instantiate one generic "User" class for each specific user.)



If you had a table called BOOKMARKS you could just limit it to one user with this SQL: select * from BOOKMARKS where USER = 'User1'; You could even save that SQL as a VIEW which is like creating a user-specific table: CREATE VIEW user1 AS select * from BOOKMARKS where USER = 'User1';



Sorry I didn't answer your question directly. It's not clear what you wanted your user tables to look like - maybe?: Table name: "User00001"

Column1 = bookmark varchar(255)

Column 2 = tag varchar(??)

Answer 3

A separate table for every user is a horrible idea, frankly...as long as userID is unique, there's no reason for it anyway. Just place your listed columns into a single table...then, if you want the data for a given user or users, just do either



SELECT FROM someTable WHERE userID =



SELECT FROM someTable WHERE userID IN

Answer 4

I want to give you the answer but this really is too easy. I am sure someone else will give you the answer anyway but in the mean time here is a little tutorial for you to read. You may even beat someone to the answer. Have fun.