|
|
Posted by --CELKO-- on 11/09/06 17:32
You have several design problems and I think that once you fix it,
changing profiles will be very easy.
You have no relational keys (IDENTITY is by definition never a key, so
you have destroyed data integrity) and more NULL-able columns than a
FORTUNE 100 payroll system. I also see that you have both
"add_email" (which is a procedure name in ISO conventions) and
"email_address" in the table; what does that mean? Why are they
logically different? If you have multiple email addresses in the data,
then use an address type to indicate this.
You have both "about_me" and "interest" which is very vague.
If they are different tell me in the data element name and add a
constraint to enforce it. What kind of interest or is interest a
percentage rate?
Why not put all the data into one table, with ISO-11179 data element
names and no redundant columns, and then create a VIEW for the profile
histories? Maybe like this:
CREATE TABLE UserProfileHistory
(email_addr VARCHAR(50) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME,
addr_type INTEGER NOT NULL,
headline VARCHAR(50) NOT NULL,
public_name VARCHAR(50) NOT NULL,
public_interests TEXT NOT NULL,
PRIMARY KEY (addr_email, start_date));
CREATE VIEW AddressList (email_addr, ..) with the last edited profile
for bulk mailings
You use a procedure to insert a new row whenever the profile is
changed. What you have done is mimic a magnetic tape file system; the
updates are on one tape whcih gets merged into the master tape. The
rows have no proper keys but do have an exposed physical locator -- a
fake record number! You used the proprietary BIT data type to mimic a
punch card or assembly language programming convention.
Navigation:
[Reply to this message]
|