Hendra Setiawan Hendra Setiawan - 23 days ago 7
Android Question

Questions about my Database MySQL and Android Studio

I want to make an app that show list of quotes to the user, and each user can favorites the quotes they like, so that the user can see all of the favorites quotes that the user favorited.

I want to make the app on Android Studio and using MySQL, not SQLite.

So if I want each user to have their own favorites list, then I must make a user table right? for login?

Is this table right?

Table User


  • user_id (Integer,Primary Key, Not null, Auto Increment)

  • email(Text or varchar?? , Not Null)

  • Password(Text or varchar??, Not Null)



Table QuoteList


  • quote_id (Integer, Primary key, Not Null, Auto Increment)

  • quotes (text, Not Null)

  • author (varchar(30), Not Null)

  • genre (I want it to have multivalue, like : motivational,love)



My Questions is:


  1. What's the correct type for email,password, and genre?

  2. Where do I put the Favorite Column, the Table User or Table QuoteList?

  3. The Favorite column should be boolean with default 0 right?



EDIT POST

So I will have to make three tables:


  1. User Table

  2. QuoteList Table

  3. UserFavourite Table



The User Favourite Table have two foreign keys, that is:


  1. user_id

  2. quote_id



and so, here is the example of populated userFavourite Table

user_id quote_id

1 2
1 3
1 4
2 4
2 6


Is it like that?

Answer

Email, password and genre can be varchar. Since you want the members to choose their favorite quotes, you need to create a relationship between User and QuoteList table.

You can create another table

UserFavorite user_id quote_id

user_id and quote_id comes from the other two tables. This way you know which user favorites which quote. user_id comes from the logged in user and quote_id comes from any quote from the list of quotes.

Comments