Contact details may be Email, Phone, Address (Home, office), Fax etc., There are various types of contacts.
Rather having to design/create the schema for user/user contacts for every application we design from scratch. It helps to have a generic model which we can just refer & use in our applications. In this article i'll show the simple & efficient schema for the same.
Source: This schema idea is taken from OfBiz framework (Database schema design of Ofbiz is considered one of the best schema design).
Basic User schema
1) USER Entity
USER_ID Integer(10) primary key, NAME Varchar (30)For simplicity sake, we just have 2 fields for User entity. You can add more columns when required.
2) CONTACT_MECH Entity
CONTACT_MECH_ID Integer(10) primary key, TYPE Varchar (10), VALUE Varchar (50)'Type' field holds a constant string values like 'PHONE', 'EMAIL', 'ADDRESS', etc., 'Value' field will be used for holding values when the value is a single value like EMAIL, WEBSITE etc.,
3) USER_CONTACT_MECH
USER_ID Integer(10) Foreign key, CONTACT_MECH_ID Integer(10) Foreign key, unique (USER_ID, CONTACT_MECH_ID)This entity is the link between User & ContactMech tables.
4) ADDRESS
CONTACT_MECH_ID Integer(10) Foreign key, ADDRESS_LINE1 Varchar(40), ADDRESS_LINE2 Varchar(40), CITY Varchar(20), STATE Varchar(20), COUNTRY Varchar(20), ZIP_CODE Varchar(10)If contact mech type is ADDRESS (HOME_ADDRESS, SHIPPING_ADDRESS etc.,), this table is looked up to get the details.
In this example, ADDRESS entity is kept separate, but Phone, email is kept as part of CONTACT_MECH. If necessary even phone can be kept as separate entity.
Using The Schema
In the application, when storing the details, based on the contact mech type, the values is stored in ContactMech and Address entity. For eg., If a webapplication has a form for user to fill in the details like Name, Email, phone & address (address1, city,country etc.,) fields. When data is submitted, the backend code will add an entry in User entity, then create Contact mech records for each of the type, i.e., Email, phone & address and store the values of email & phone in ContactMech table, and create a entry in Address entity to store address details. Finally create entries in UserContactMech to map between User & ContactMech records.
Sample Java (jsp) code for Storing details would be something like this:
String name = request.getParameter("name");
String email = request.getParameter("email");
String phone = request.getParameter("phone");
String address1 = request.getParameter("address1");
String city = request.getParameter("city");
String country = request.getParameter("country");
String zipcode = request.getParameter("zipcode");
int status = 0;
// Insert new record for User table
String userId = DBUtil.getNextId ("USER");
String insertUserSQL = "INSERT INTO USER (USER_ID, NAME) VALUES (?,?)";
pstatement1 = connection.prepareStatement(insertUserSQL);
pstatement1.setString(1, userId);
pstatement1.setString(2, name);
status = pstatement.executeUpdate();
String nextContactMechId = "";
// Create entry in Contact Mech for Email
nextContactMechId = DBUtil.getNextId ("CONTACT_MECH");
String insertEmailSQL = "INSERT INTO CONTACT_MECH (CONTACT_MECH_ID, TYPE, VALUE) VALUES (?, ?, ?)";
pstatement2 = connection.prepareStatement(insertEmailSQL);
pstatement2.setString(1, nextContactMechId);
pstatement2.setString(2, "EMAIL");
pstatement2.setString(1, email);
status = pstatement2.executeUpdate();
String insertUserContactMechSQL = "INSERT INTO USER_CONTACT_MECH (USER_ID, CONTACT_MECH_ID) VALUES (?, ?)";
pstatement2a = connection.prepareStatement(insertUserContactMechSQL);
pstatement2a.setString(1, userId);
pstatement2a.setString(2, nextContactMechId);
status = pstatement2a.executeUpdate();
// Create entry in Contact Mech for Address
nextContactMechId = DBUtil.getNextId ("CONTACT_MECH");
String insertAddressSQL = "INSERT INTO ADDRESS (CONTACT_MECH_ID, ADDRESS_LINE1, CITY, COUNTRY, ZIPCODE) ";
insertAddressSQL += " VALUES (?, ?, ?, ?, ?) ";
pstatement3 = connection.prepareStatement(insertAddressSQL);
pstatement3.setString(1, nextContactMechId);
pstatement3.setString(2, address1);
pstatement3.setString(3, city);
pstatement3.setString(4, country);
pstatement3.setString(5, zipcode);
status = pstatement3.executeUpdate();
String insertContactMechSQL = "INSERT INTO CONTACT_MECH (CONTACT_MECH_ID, TYPE, VALUE) VALUES (?, ?)";
pstatement3a = connection.prepareStatement(insertContactMechSQL);
pstatement3a.setString(1, nextContactMechId);
pstatement3a.setString(2, "HOME_ADDRESS");
status = pstatement3a.executeUpdate();
// Value field is left empty
pstatement3b = connection.prepareStatement(insertUserContactMechSQL);
pstatement3b.setString(1, userId);
pstatement3b.setString(2, nextContactMechId);
status = pstatement3b.executeUpdate();
// Create entry for Phone
/* Is similar to creating for EMAIL as above */
DBUtil.getNextId() is a utility method, which gets the next id to use for the specified entity name.
(See my other post on How to generate unique ID by own, rather using Auto-increment.)
Similary, when retrieving the details for displaying, based on the type, the data is fetched from appropriate table.
Example Java code for retrieving is as below: (Get details for userId passed as parameter)
String userId = request.getParameter("user_id");
// Values for these will be populated from database
String name = null;
String email = null;
String phone = null;
String address1 = null;
String city = null, country = null, zipcode = null;
String sql = "SELECT USER_ID, NAME FROM USER WHERE USER_ID = '" + userId + "'";
stmt=con.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()) {
name = rs.getString("NAME");
}
// Get list of contact mech id's
sql = "SELECT CONTACT_MECH_ID FROM USER_CONTACT_MECH WHERE USER_ID = '" + userId + "'";
stmt1 = con.createStatement();
rs1 = stmt.executeQuery(sql);
while (rs1.next()) {
String contactMechId = rs1.getString ("CONTACT_MECH_ID");
// Get ContactMech for this contactMechId
String sql_temp = "SELECT CONTACT_MECH_ID, TYPE, VALUE FROM CONTACT_MECH WHERE CONTACT_MECH_ID = '" + contactMechId + "'";
stmt2 = con.createStatement();
rs2 = stmt.executeQuery(sql_temp);
while (rs2.next()) {
String type = rs2.get("TYPE");
if("EMAIL".equalsIgnoreCase (type)) {
email = rs2.get("VALUE");
} else if("PHONE". equalsIgnoreCase (type) ) {
phone = rs2.get("VALUE");
} else if ("HOME_ADDRESS".equalsIgnoreCase (type) || "SHIPPING_ADDRESS".equalsIgnoreCase (type) ) {
sql_temp = "SELECT ADDRESS_LINE1, CITY, COUNTRY, ZIPCODE FROM ADDRESS WHERE CONTACT_MECH_ID = '" + contactMechId + "'";
stmt3 = con.createStatement();
rs3 = stmt.executeQuery(sql_temp);
while(rs3.next()) {
address1 = rs3.getString ("ADDRESS_LINE1");
city = rs3.getString ("CITY");
country = rs3.getString ("COUNTRY");
zipcode = rs3.getString ("ZIPCODE");
}
}
} // End of rs2 loop
}
// Display the details.
out.println ("Name: " + name );
out.println ("Phone: " + phone );
out.println ("Email: " + email );
out.println ("Address: " + address1 + ", " + city + ", " + country + "," + zipcode);
Note: If you use any ORM like Hibernate, the storage/retrieval process will be even easier. Only the mapping between bean & database tables should be done properly.
Posts
No comments:
Post a Comment