I'm going to start small here because I actually have several questions but I'll go one at a time.
In the app I am making to better learn Java/Android/SQL/OOP, I have a page where a user can define Places (e.g. "New York City", "Seattle", etc). You could add made-up stuff if you wanted, too, doesn't matter. The user can perform basic CRUD operations on these Places.
Each Place also has a PlaceCategory, which is also user-defined elsewhere in the app in a separate list. So maybe I associate NYC and Boston with "The Northeast" and so if I click the filter dropdown and select "The Northeast," then NYC and Boston remain and the rest disappear (until I remove the filter again).
This is my SQL for the PlaceCategory table:
private static final String STRING_CREATE_TABLE_PLACE_CATEGORIES =
"CREATE TABLE PLACE_CATEGORIES ("
+ "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
+ "CATEGORY_NAME TEXT UNIQUE NOT NULL)";
And for the object:
public class PlaceCategory implements Comparable<PlaceCategory> {
private long mId;
private String mName;
public PlaceCategory() {
mId = -1;
mName = "";
}
public PlaceCategory(long id, String name) {
mId = id;
mName = name;
}
public long getId() {
return mId;
}
public void setId(long id) {
mId = id;
}
public String getName() {
return mName;
}
public void setName(String name) {
mName = name;
}
@Override
public int compareTo(PlaceCategory another) {
return mName.compareTo(another.getName());
}
}
And now for Places:
private static final String STRING_CREATE_TABLE_PLACES =
"CREATE TABLE PLACES ("
+ "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
+ "PLACE_CATEGORY_ID INTEGER NOT NULL, "
+ "PLACE_NAME TEXT NOT NULL, "
+ "PLACE_DESC TEXT NOT NULL, "
+ "FOREIGN KEY("
+ "PLACE_CATEGORY_ID) REFERENCES "
+ "PLACE_CATEGORIES(ID) ON DELETE CASCADE, "
+ "UNIQUE("
+ "PLACE_CATEGORY_ID,"
+ "PLACE_NAME))";
And the object:
public class Place implements Comparable<Place>{
private long mId;
private long mCategoryId;
private String mName;
private String mDesc;
public Place() {
mId = -1;
mCategoryId = -1;
mName = "";
mDesc = "";
}
public Place(long id, long categoryId, String name, String desc) {
mId = id;
mCategoryId = categoryId;
mName = name;
mDesc = desc;
}
public long getId() {
return mId;
}
public void setId(long id) {
mId = id;
}
public long getCategoryId() {
return mCategoryId;
}
public void setCategoryId(long categoryId) {
mCategoryId = categoryId;
}
public String getName() {
return mName;
}
public void setName(String name) {
mName = name;
}
public String getDesc() {
return mDesc;
}
public void setDesc(String desc) {
mDesc = desc;
}
@Override
public int compareTo(Place another) {
return mName.compareTo(another.getName());
}
}
My questions:
Should I be using
long
orLong
for the ids in general? I usedlong
because I don't think I need the null-ability ofLong
but I am unsure what the accepted practice is here.Should I be storing the Category as an object in the Place, as opposed to an id like I am currently doing? My concern with storing only the id is that I would have to query the database for the category details whenever I wanted more detail. I don't know if this is considered a good idea. On the other hand, it is easier to query/generate Places when their members are primitive datatypes that can be ripped straight out of the table.
Are the ways my tables set up normalized properly? Do they make sense?
Anything else that stands out?