Jdbc table design which is better

问题:

Hi I'm creating a notification system which consists of only 3 data inputs 1. email - to which the notification should be sent 2. the notification message - varchar 3. status - sent or not (y or N)

--Note the final goal is club all notification to be sent to an email and send it as one email by a batch job

Help me choosing which design is better

Design -1 create table Notifications( notification_id integer auto_increment primary key, message varchar(100) not null );

create table Emails( email_id integer not null auto_increment primary key, email varchar(40) not null );

create table Email_notifications( email_id integer not null, notification_id integer , status varchar(5) not null,

foreign key(email_id) references Emails(email_id), foreign key(notification_id) references Notifications(notification_id), primary key(email_id, notification_id) );

Design-2:

create table batchnotifications( id integer not null auto_increment primary key, email varchar(40) not null, message varchar(100) not null, status varchar(5) not null default 'N' );

Since i'm going to use JDBC in it let me know in that perspective in terms of ease of api creation.

回答1:

You should use Design 1 Its better to implement.
you can use Design-2 also but if you have to send status to multiple people with different emails and notifications,then it is possible only with Design-1
Let's assume one condition :
If you have to send email with id 2 and notification with id 4 then in that case, you need two different tables for email and notification. which you are doing in Design-1.
AND
Let's assume another condition :
If you have to send unique email and notification with same id then use Design-2

回答2:

Design 1 is future proof. Design 2 is still correct with respect of normalization rules, assuming that:

• you will not add later content like "Name", "Reputation", etc to the email. In that moment, usage of Design 1 is mandatory.
• the relative large key of "email" is not a space/performance problem, as compared to the integer key.

The driver you use to connect (JDBC, DAO, ODBC, OLEDB or native) is irrelevant with respect of the data structure.