0

I am designing a voting application and designed a database with 2 tables - State and District. The two tables are -

State details table

State_ID NUMBER(3)

State_name VARCHAR2(30)

PRIMARY KEY(State_ID)

District details table

District_ID NUMBER(3)

State_ID NUMBER(3)

District_name VARCHAR2(30)

PIN_Code NUMBER(6)

PRIMARY KEY(District_ID)

UNIQUE(State_ID, District_name)

FOREIGN KEY(State_ID)

As two states can have a district with same name, I am considering the combination of State ID and District name as UNIQUE by combination.

Now I have to design JPA Entities for these two tables, but I am unable to design because, in the database design, the District table has State ID in it as Foreign Key, but when it comes to designing entities having a State object inside District sounds meaningless, because if I keep HAS-A relationship in my mind, then District doesn't have a State in it. A State HAS-A list of Districts. This is not in accord with the above database design.

Can someone please help me in designing JPA Entities for this. Please suggest if the database design needs modification too. Thanks.

Improve this question
2
  • 2
    Think of it as a parent child relationship. The State has districts and the district is within a state. en.wikibooks.org/wiki/Java_Persistence/… Commented Mar 6, 2015 at 15:20
  • Thanks @AlanHay for suggesting the link. Commented Mar 6, 2015 at 18:43

2 Answers 2

Reset to default
1

An example JPA approach based on Rick's proposal:

@Entity
@Table(name = "States")
public class State {
    @Id
    @Column(nullable = false, columnDefinition = "CHAR(2) CHARACTER SET ascii")
    private String code;

    @Column(nullable = false, length = 30)
    private String name;

    @OneToMany(mappedBy = "state")
    private Collection<District> districts;

    public State() { }

    // getters, setters, etc.
}

@Entity
@Table(name = "Districts", uniqueConstraints = { 
    @UniqueConstraint(columnNames = { "state_code", "name" })
})
public class District {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(nullable = false, columnDefinition = "SMALLINT UNSIGNED")
    private short id;

    @Column(name = "state_code", nullable = false, 
            columnDefinition = "CHAR(2) CHARACTER SET ascii")
    private String code;

    @Column(length = 30)
    private String name;

    @ManyToOne
    @JoinColumn(name = "state_id")
    private State state;

    public District() { }

    // getters, setters, etc.
}

NOTE: due to usage of columnDefinition attribute the above entities are not portable across databases.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
CREATE TABLE States (
    code CHAR(2) NOT NULL CHARACTER SET ascii,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY(code)
);
CREATE TABLE Districts
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    state_code CHAR(2) NOT NULL CHARACTER SET ascii,
    ...
    PRIMARY KEY(id),
    UNIQUE (state_code, name)
);

Notes:
* May as well use the standard state codes (AK, AL, ...) if you are talking about the US.
* Probably the only purpose of States is to spell out the name.
* I was explicit about ascii in case your table defaults to utf8.
* MySQL uses VARCHAR, not VARHAR2.
* You could get rid of Districts.id and have simply PRIMARY KEY(state_code, name), but I am guessing you have a bunch of other tables that need to join to this one, and a 2-byte id would be better than the bulky alternative.

Improve this answer

2 Comments

Thanks @Rick for suggesting me ideas to improve the database design. But my real question is how to figure those tables as JPA Entities? In the database design the district table has a state code in it. But if I figure the same design as an object, a State HAS-A District, but the District doesn't have a State in it. I am sorry if my question is unclear about it.
Are you talking about "Java Persistence API"? I know nothing about it. I assumed there were many "districts" in each "state", and no district was partially in each of two states. That's called, in different circles, a Many-to-one (N:1) relationship. (Many Districts to One State). That's what I coded. I agree that "district" and "state" are "entities". N:1 does not need an extra table, just one column. Many-to-many (M:N) does need an extra table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.