Android database question, need advice and suggestions

Question

I currently facing problem where user have the choice to enter several working experience, where every working experience include several details (company name, duration, roles etc) I don't know how should I store it since users have the freedom to add as many working experience as they have.

I have a table as below

private static final String DATABASE_CREATE_WORKEXP =
        "create table workexp (work_id integer primary key, _id integer not null," +
        "workcompany text, workduration text, workrole text, workskills text" +
        "workproject text, worksalary integer, workreason text," +
        "foreign key(_id) references user(_id));";

I have a problem while trying to insert/update and delete entries as company name can be duplicated. Any advice or suggestion? I'm glad to provide more info for you to help me.

THANKS!

Answer 1

If you want to avoid duplicate company names, they should be managed in their own table. Then you'll have to add some UI to pick a company name in the table of existing companies or create a new one.

Answer 2

every db table should have an id field, which has the primary_key attribute. the id is unique and can not be dublicated. never use a name as primary key unless it is not unique.

Before implementing databases in android think about your idea.

First you can store the data on the device. But this solution hase a big disatvantage. If your app is deleted from the device all data is lost.

On the other hand you can store the data online, on a webserver and access it through a REST API which I think is a more comfortable solution. It is up to you.