|
|
Posted by pbd22 on 11/09/06 19:15
--CELKO-- wrote:
> 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)
i am using email addresses as relational keys. each and every table has
an email
associated with each row. data is accessible in this way.
>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.
add_email describes a profile that user "email_address" has added to
his AddList.
i am not aware of address types. i have been entering email addresses
as VARCHARS
which has not caused me any problems so far. is there a reason why i
should be
concerned about 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?
this is part of the profile owner's description. about_me is TEXT and
describes
the users background. interests, again TEXT, is more specific. How
would
a constraint matter?
> 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.
I am not sure if that is correct about the BIT values. The value
indicates an action
taken, such as a rejected or accepted request, and I dont see how else
to do this.
or, i dont see the point to improving on this method. what is being
harmed by this?
I like the idea of having an overall table for my profiles, as there
are about 30
individual tables at this point that describe different features of the
profile (EditProfile,
AddList, PersonalPhotos, etc). I am not quite sure how to procede doing
this. I'll
do some reading on this, in the mean time; am i doing any harm by
continuing to
build my system the way i am? can the aggregate table be created at a
later date
without much reverse engineering?
i thank you for your constructive feedback.
regards.
Navigation:
[Reply to this message]
|