how to store days in db? 2 options, your comment

(newbie to db)

this might be a trivial question but it really keeps me wonder:

say I have the following class:

long id;

int[] days = new int[7];

assuming the days array can have upto 7 slots with values 0,1,2,3... (0=sat)

my question is how to store this in a db? I can do somthing like this:

id.........days

1001

1002

1003

1004

1005

or, I can do this:

long id;

String days;

and to store it will be:

id.........days

10012345

later, when I read the value of days I can simply parse it.

well, the first solution seems more right but the second solution seems quicker.

can anyone comment?

thanks

[732 byte] By [xianwinwina] at [2007-11-26 16:25:52]
# 1

this isn't a good normalized relational design.

what are you really storing here? an array of seven ints is hardly an example of good object-oriented design.

i don't like either option. both are potentially very bad. you should not be parsing anything you get from a database.

neither design is good. give more details about what you're really doing.

%

duffymoa at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 2

Thanks duffymo.

here's what I'm trying to accomplish:

I need to check that a car rental is in conjunction with a contract. example:

car rental:

long carID;

long usreID;

Date rentalStart;

Date rentalEnd;

int DAY_OF_RENTAL

.

the contract:

int[] DAY_OF_RENTAL = new int[7];

Date contractStart;

Date contractEnd;

.

.

now, lets say that the contract says this: if a user rents a car on MON or TUE or SAT he's entitle to receive 10% discount.

I need to check the contract's valid days against the rental day to provide 10% discount. So for this, I have to store the DAY_OF_RENTALS.

I thought on:

contractID.....DAY_OF_RENTALS

10010

10012

10013

If you need more info, please let me know .

What do you think

THANK YOU

xianwinwina at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 3

> Thanks duffymo.

>

> here's what I'm trying to accomplish:

>

> I need to check that a car rental is in conjunction

> with a contract. example:

>

> car rental:

> long carID;

> long usreID;

> Date rentalStart;

> Date rentalEnd;

> int DAY_OF_RENTAL

> .

>

>

> the contract:

> int[] DAY_OF_RENTAL = new int[7];

This is what I disagree with. Why do you need an array of ints? And why do you have to store this in a database?

The rule is ambiguous. Does this mean that if the contractStart date falls on those days of the week that you get the discount? Or do you get the discount on every MON, TUE, and SAT during the life of the contract?

> Date contractStart;

> Date contractEnd;

> .

> .

>

> now, lets say that the contract says this: if a user

> rents a car on MON or TUE or SAT he's entitle to

> receive 10% discount.

>

> I need to check the contract's valid days against the

> rental day to provide 10% discount. So for this, I

> have to store the DAY_OF_RENTALS.

>

> I thought on:

> contractID.....DAY_OF_RENTALS

> 10010

> 10012

> 10013

>

>

> If you need more info, please let me know .

> What do you think

> THANK YOU

I think storing the contract begin and end dates in the database is sufficient.Give the Java object the smarts to apply the business rules when it calculates the price.

The rules will be dynamic, as they are in most businesses, so I'd give your object a way to change the rules in a flexible way.

%

duffymoa at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 4

My advice would be to not worry about the database until you have the problem modeled well with objects. Get that working and then persist the objects.

You can get this problem working nicely without a database. You have to stop thinking about tables and columns and start thinking about objects. Java is an object-oriented language, after all.

%

duffymoa at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 5

This has been a fun problem to think about. I've had a great time tonight.

What I'm going to post will be over your head, but it illustrates what I mean when I advise you to think about the objects first.

I've written a little RentalAgency application that runs on the command line. It lets me associate a Customer with a Vehicle in a Contract object, which contains the start and end dates of the agreement.

The interesting bit comes in the PricingStrategy interface. Your rules for a discount on certain days made me think about how someone might write this in a such a way that they could easily change the business rules without having to rewrite the code.

I can change the pricing rules at will just by implementing a new class. I've included four examples for you to think about.

Notice how simple the object model is. I think it matches my understanding of how this might work pretty well. It's thirteen classes and interfaces, but not difficult to understand once you have it all in one spot.

See what you think. Thanks again for a fun problem. - %

Name.java

package rental;

import java.io.Serializable;

/**

* Encapsulation of a Name

* Date: Jan 27, 2007

* Time: 6:40:30 PM

*/

public class Name implements Serializable

{

private String first;

private String last;

public Name(String first, String last)

{

this.first = first;

this.last = last;

}

public String getFirst()

{

return first;

}

public String getLast()

{

return last;

}

public boolean equals(Object o)

{

if (this == o)

{

return true;

}

if (o == null || getClass() != o.getClass())

{

return false;

}

Name name = (Name) o;

if (first != null ? !first.equals(name.first) : name.first != null)

{

return false;

}

if (last != null ? !last.equals(name.last) : name.last != null)

{

return false;

}

return true;

}

public int hashCode()

{

int result;

result = (first != null ? first.hashCode() : 0);

result = 31 * result + (last != null ? last.hashCode() : 0);

return result;

}

public String toString()

{

return new StringBuilder().append("Name{").append("first='").append(first).append('\'').append(", last='").append(last).append('\'').append('}').toString();

}

}

VinFactory.java

package rental;

import java.util.Random;

/**

* Encapsulates the rules for generating a VIN

* Date: Jan 27, 2007

* Time: 6:48:23 PM

* @link http://en.wikipedia.org/wiki/Vehicle_identification_number

*/

public class VinFactory

{

private static final char [] LEGAL_CHARS =

{

'0', '1', '2', '3', '4', '5', '6', '7', '8', '9',

'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',

'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',

'U', 'V', 'W', 'X', 'Y', 'Z',

};

private static final int DEFAULT_LENGTH = 17;

private static final String DEFAULT_MANUFACTURER = "JT"; // Only renting Toyotas

private static Random random = new Random();

/**

* Generate a 17-digit code for VIN

* @return VIN

*/

public static String getVin()

{

return getVin(DEFAULT_MANUFACTURER);

}

public static String getVin(String manufacturer)

{

StringBuilder vin = new StringBuilder(DEFAULT_LENGTH);

vin.append(manufacturer);

vin.append(getRandomString(DEFAULT_LENGTH-manufacturer.length()));

return vin.toString();

}

private static String getRandomString(int length)

{

StringBuilder builder = new StringBuilder(length);

for (int i = 0; i < length; ++i)

{

builder.append(LEGAL_CHARS[random.nextInt(LEGAL_CHARS.length)]);

}

return builder.toString();

}

}

Address.java

package rental;

import java.io.Serializable;

/**

* Classic Address

* Date: Jan 27, 2007

* Time: 6:41:50 PM

*/

public class Address implements Serializable

{

public static final String DEFAULT_STREET1 = "123 Main Street";

public static final String DEFAULT_STREET2 = "Apartment 4F";

public static final String DEFAULT_CITY = "Anytown";

public static final String DEFAULT_STATE = "NY";

public static final String DEFAULT_ZIP = "01010";

private Long id;

private String street1;

private String street2;

private String city;

private String state;

private String zip;

public Address()

{

this(DEFAULT_STREET1, DEFAULT_STREET2, DEFAULT_CITY, DEFAULT_STATE, DEFAULT_ZIP);//To change body of created methods use File | Settings | File Templates.

}

public Address(String street1, String street2, String city, String state, String zip)

{

this(null, street1, street2, city, state, zip);

}

public Address(Long id, String street1, String street2, String city, String state, String zip)

{

this.id = id;

this.street1 = street1;

this.street2 = street2;

this.city = city;

this.state = state;

this.zip = zip;

}

public Long getId()

{

return id;

}

public String getStreet1()

{

return street1;

}

public String getStreet2()

{

return street2;

}

public String getCity()

{

return city;

}

public String getState()

{

return state;

}

public String getZip()

{

return zip;

}

public boolean equals(Object o)

{

if (this == o)

{

return true;

}

if (o == null || getClass() != o.getClass())

{

return false;

}

Address address = (Address) o;

if (id != null ? !id.equals(address.id) : address.id != null)

{

return false;

}

return true;

}

public int hashCode()

{

return (id != null ? id.hashCode() : 0);

}

public String toString()

{

return new StringBuilder().append("Address{").append("id=").append(id).append(", street1='").append(street1).append('\'').append(", street2='").append(street2).append('\'').append(", city='").append(city).append('\'').append(", state='").append(state).append('\'').append(", zip='").append(zip).append('\'').append('}').toString();

}

}

Customer.java

package rental;

import java.io.Serializable;

/**

* Customer for rental system

* Date: Jan 27, 2007

* Time: 6:39:54 PM

*/

public class Customer implements Serializable

{

private Long id;

private Name name;

private Address address;

public Customer(Name name, Address address)

{

this(null, name, address);

}

public Customer(Long id, Name name, Address address)

{

this.id = id;

this.name = name;

this.address = address;

}

public boolean equals(Object o)

{

if (this == o)

{

return true;

}

if (o == null || getClass() != o.getClass())

{

return false;

}

Customer customer = (Customer) o;

if (id != null ? !id.equals(customer.id) : customer.id != null)

{

return false;

}

return true;

}

public int hashCode()

{

return (id != null ? id.hashCode() : 0);

}

public String toString()

{

String newline = System.getProperty("line.separator");

return new StringBuilder().append("Customer{").append("id=").append(id).append(newline).append(name).append(newline).append(address).append('}').toString();

}

}

Vehicle.java

package rental;

import java.io.Serializable;

/**

* Vehicle for rental system

* Date: Jan 27, 2007

* Time: 6:37:28 PM

*/

public class Vehicle implements Serializable

{

private Long id;

private String vin;

private String make;

private String model;

private int year;

public Vehicle(String make, String model, int year)

{

this(null, VinFactory.getVin(), make, model, year);

}

public Vehicle(Long id, String vin, String make, String model, int year)

{

this.id = id;

this.vin = vin;

this.make = make;

this.model = model;

this.year = year;

}

public Long getId()

{

return id;

}

public String getVin()

{

return vin;

}

public String getMake()

{

return make;

}

public String getModel()

{

return model;

}

public int getYear()

{

return year;

}

public boolean equals(Object o)

{

if (this == o)

{

return true;

}

if (o == null || getClass() != o.getClass())

{

return false;

}

Vehicle vehicle = (Vehicle) o;

if (id != null ? !id.equals(vehicle.id) : vehicle.id != null)

{

return false;

}

return true;

}

public int hashCode()

{

return (id != null ? id.hashCode() : 0);

}

public String toString()

{

return new StringBuilder().append("Vehicle{").append("id=").append(id).append(", vin='").append(vin).append('\'').append(", make='").append(make).append('\'').append(", model='").append(model).append('\'').append(", year=").append(year).append('}').toString();

}

}

Contract.java

package rental;

import java.io.Serializable;

import java.util.Date;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

/**

* Contract for our rental system

* Date: Jan 27, 2007

* Time: 7:22:09 PM

*/

public class Contract implements Serializable

{

public static final DateFormat DEFAULT_DATE_FORMAT = new SimpleDateFormat("EEE dd-MMM-yyyy");

private Long id;

private Customer customer;

private Vehicle vehicle;

private Date begDate;

private Date endDate;

public Contract(Customer customer, Vehicle vehicle, Date begDate, Date endDate)

{

this(null, customer, vehicle, begDate, endDate);

}

public Contract(Long id, Customer customer, Vehicle vehicle, Date begDate, Date endDate)

{

if (endDate.before(begDate))

throw new IllegalArgumentException("end date must follow beg date");

this.id = id;

this.customer = customer;

this.vehicle = vehicle;

this.begDate = new Date(begDate.getTime());

this.endDate = new Date(endDate.getTime());

}

public Long getId()

{

return id;

}

public Customer getCustomer()

{

return customer;

}

public Vehicle getVehicle()

{

return vehicle;

}

public Date getBegDate()

{

return begDate;

}

public Date getEndDate()

{

return endDate;

}

public double getPrice(PricingStrategy pricingStrategy)

{

return pricingStrategy.calculatePrice(this);

}

public boolean equals(Object o)

{

if (this == o)

{

return true;

}

if (o == null || getClass() != o.getClass())

{

return false;

}

Contract contract = (Contract) o;

if (id != null ? !id.equals(contract.id) : contract.id != null)

{

return false;

}

return true;

}

public int hashCode()

{

return (id != null ? id.hashCode() : 0);

}

public String toString()

{

String newline = System.getProperty("line.separator");

return new StringBuilder().append("Contract{").append("id=").append(id).append(newline).append(customer).append(newline).append(vehicle).append(newline).append("begDate=").append(begDate).append(newline).append("endDate=").append(endDate).append('}').toString();

}

}

PricingStrategy.java

package rental;

/**

* Interface for generating pricing policies

* Date: Jan 27, 2007

* Time: 7:23:31 PM

*/

public interface PricingStrategy

{

double calculatePrice(Contract contract);

int getNumberOfDays(Contract contract);

}

AbstractPricingStrategy.java

package rental;

import java.util.Calendar;

/**

* Abstract class that all pricing strategies extend

* Date: Jan 27, 2007

* Time: 8:32:29 PM

*/

public abstract class AbstractPricingStrategy implements PricingStrategy

{

public int getNumberOfDays(Contract contract)

{

int numberOfDays = 0;

Calendar calendar = Calendar.getInstance();

calendar.setTime(contract.getBegDate());

while (calendar.getTime().before(contract.getEndDate()))

{

++numberOfDays;

calendar.add(Calendar.DAY_OF_YEAR, 1);

}

return numberOfDays;

}

}

FlatRatePricingStrategy.java

package rental;

/**

* A simple pricing strategy: every contract, every customer pays a flat rate

* Date: Jan 27, 2007

* Time: 7:51:22 PM

*/

public class FlatRatePricingStrategy extends AbstractPricingStrategy

{

protected double price;

public FlatRatePricingStrategy(double price)

{

if (price < 0.0)

throw new IllegalArgumentException("price cannot be negative");

this.price = price;

}

public double calculatePrice(Contract contract)

{

return price;

}

}

ConstantDailyRatePricingStrategy.java

package rental;

/**

* Constant daily rate treats the price as dollars per day

* Date: Jan 27, 2007

* Time: 8:19:03 PM

*/

public class ConstantDailyRatePricingStrategy extends FlatRatePricingStrategy

{

public ConstantDailyRatePricingStrategy(double price)

{

super(price);

}

public double calculatePrice(Contract contract)

{

return super.calculatePrice(contract)*getNumberOfDays(contract);

}

}

DiscountPricingStrategy.java

package rental;

import java.util.Arrays;

import java.util.Calendar;

import java.util.List;

/**

* Pricing strategy that gives a percentage discount on special days

* Date: Jan 27, 2007

* Time: 7:52:04 PM

*/

public class DiscountPricingStrategy extends FlatRatePricingStrategy

{

private static final List<Integer> DISCOUNT_DAYS = Arrays.asList( Calendar.MONDAY, Calendar.TUESDAY, Calendar.SATURDAY);

private double percentage;

public DiscountPricingStrategy(double price, double percentage)

{

super(price);

this.percentage = percentage;

}

public double calculatePrice(Contract contract)

{

double total = 0.0;

Calendar calendar = Calendar.getInstance();

calendar.setTime(contract.getBegDate());

while (calendar.getTime().before(contract.getEndDate()))

{

if (DISCOUNT_DAYS.contains(calendar.get(Calendar.DAY_OF_WEEK)))

{

total += price*(1.0-percentage);

}

else

{

total += price;

}

calendar.add(Calendar.DAY_OF_YEAR, 1);

}

return total;

}

}

MinimumDaysPricingStrategy.java

package rental;

/**

* Pricing strategy that enforces a minimum number of days rule

* Date: Jan 27, 2007

* Time: 9:20:24 PM

*/

public class MinimumDaysPricingStrategy extends FlatRatePricingStrategy

{

private int minNumberOfDays;

public MinimumDaysPricingStrategy(double price, int minNumberOfDays)

{

super(price);

if (minNumberOfDays < 0)

throw new IllegalArgumentException("minimum number of days cannot be negative");

this.minNumberOfDays = minNumberOfDays;

}

public double calculatePrice(Contract contract)

{

return super.calculatePrice(contract)*Math.max(minNumberOfDays, getNumberOfDays(contract));

}

}

RentalAgency.java

package rental;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.text.ParseException;

import java.text.NumberFormat;

import java.util.Date;

/**

* Rental agency model

* Date: Jan 27, 2007

* Time: 8:02:08 PM

*/

public class RentalAgency

{

public static final String DEFAULT_MAKE = "Toyota";

public static final String DEFAULT_MODEL = "Camry";

public static final int DEFAULT_YEAR = 2007;

public static final DateFormat DEFAULT_DATE_FORMAT = new SimpleDateFormat("dd-MMM-yyyy");

public static final double DEFAULT_DAILY_RATE = 50.0; // dollars per day or flat rate in dollars

public static final double DEFAULT_PERCENT_DISCOUNT = 0.10; // ten percent discount on discount days

public static final int DEFAULT_MIN_DAYS = 7; // default minimum number of days; gotta take a vehicle for a week.

public static void main(String [] args)

{

try

{

if (args.length > 3)

{

String first = args[0];

String last = args[1];

Date begDate = DEFAULT_DATE_FORMAT.parse(args[2]);

Date endDate = DEFAULT_DATE_FORMAT.parse(args[3]);

Customer customer = new Customer(new Name(first, last), new Address());

Vehicle vehicle = new Vehicle(DEFAULT_MAKE, DEFAULT_MODEL, DEFAULT_YEAR);

Contract contract = new Contract(customer, vehicle, begDate, endDate);

//PricingStrategy pricingStrategy = new FlatRatePricingStrategy(DEFAULT_DAILY_RATE);

//PricingStrategy pricingStrategy = new ConstantDailyRatePricingStrategy(DEFAULT_DAILY_RATE);

//PricingStrategy pricingStrategy = new DiscountPricingStrategy(DEFAULT_DAILY_RATE, DEFAULT_PERCENT_DISCOUNT);

PricingStrategy pricingStrategy = new MinimumDaysPricingStrategy(DEFAULT_DAILY_RATE, DEFAULT_MIN_DAYS);

System.out.println(contract);

System.out.println("# days: " + pricingStrategy.getNumberOfDays(contract));

System.out.println("price: " + NumberFormat.getCurrencyInstance().format(contract.getPrice(pricingStrategy)));

}

else

{

System.out.println("Usage: rental.RentalAgency [first] [last] [start] [end]");

}

}

catch (ParseException e)

{

e.printStackTrace();

}

}

}

duffymoa at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 6

Did a little refactoring.

I decided that a Contract should be able to tell me its duration in days, so I moved that method. This lets me get rid of the AbstractPricingStrategy class. PricingStrategy does nothing but calculate price.

I added a PricingStrategyFactory class to let me change things more easily.

If I were serious about this I'd add a Money class that would encapsulate dollars, cents, and Currency.

%

duffymoa at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 7

WOW.

thanks duffymo. I just played with the code (debug it to get familiar with) and it looks great (pure OOP).

I would use this concept in my application.

now, in the DiscountPricingStrategy you wrote the calculatePrice method. Assuming the rental agency would change the days of discount, how would you 'save' it in the db?

thanks again for all the effort and time!

xianwinwina at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 8

> WOW.

> thanks duffymo. I just played with the code (debug it

> to get familiar with) and it looks great (pure OOP).

>

> I would use this concept in my application.

Careful. Depending on your experience level, your professor might notice the profound change in your style. It could be rather obvious that this isn't yours.

> now, in the DiscountPricingStrategy you wrote the

> calculatePrice method. Assuming the rental agency

> would change the days of discount, how would you

> 'save' it in the db?

As you can see, I hardwired the discount days in a List. You'd want to abstract that so you could modify the database to change the strategy.

You'll want a polymorphic kind of query if you intend to keep the PricingStrategy objects in the database. Perhaps one table per concrete type will work. You'll want a 1:m relationship between the DiscountPricingStrategy and a DiscountDays table.

I'd recommend that you have a separate persistence layer. Start with Data Access Object (DAO) interfaces for the model classes. The PricingStrategyDao will be the most interesting and challenging one.

> thanks again for all the effort and time!

My pleasure. I did it for myself. I enjoyed thinking about the problem.

%

duffymoa at 2007-7-8 22:49:57 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...