Customer Form

Hello Experts,

I would like some advice / feedback on proper column names, data types and lengths for the following I need to collect from my customers. Also, PLEASE let me know if I can improve the type of information that I'm collecting.

Display Name As
Company Name
First Name
Last Name
Address
City
State
Zip Code
Phone Number
Mobile Phone Number
Email Address
LVL 4
asp_net2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
For starters...
CompanyName varchar(100), 
FirstName varchar(50), 
LastName varchar(50), 
Address1 varchar(100), 
Address2 varchar(100), 
City varchar(50), 
State char(2), 
ZipCode varchar(9), 
PhoneNumber varchar(25), 
MobilePhoneNumber varchar(25), 
EmailAddress varchar(100)

Open in new window

  • The above assumes standard English names.  If you have to deal with any international languages that would require Unicode characters to store then change varchar's to nvarchar's.
  • People change companies all the time, so consider a separate table for Company and then a M:M table between Company and Person.
  • Refrain from using spaces in column names, as that forces code to use square brackets [ ] when referring to the name, which is one thing to potentially forget and throw an error.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asp_net2Author Commented:
Thank you for that info Jim. Yes, will be using English names and customers would only be in the US. Also, I forgot to include Display Name As "DisplayNameAs". There is alot of redundancy data that I'm collecting and not sure if this is a good approach. Not every customer is a "Company". So when I retrieve a list of ALL Customers I need to have a field name like Display Name As in my DB that shows a single Company or Person.
Jim HornSQL Server Data DudeCommented:
>Not every customer is a "Company"
As long as the column is defined NOT NULL you'll be fine.  NULL is the default unless explicitly stated.  Also it wouldn't be the worst thing in the world to learn about your data as it is collected, and then make minor changes.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

asp_net2Author Commented:
Ok, I wasn't sure if there was a field or two that I could use instead of having fields called DisplayNameAs, CompanyName, FirstName and LastName.
Scott FellDeveloper & EE ModeratorCommented:
In addition to the good advice from Jim.  I suggest adding some fields.

ID - some type of unique ID.  There are great debates on what is best to use for an ID field. For most, it is easiest to  use an auto-generated integer.

DateCreated -  datetime, defaults to getdate() and never changes.  Allows you to know when the record was last created.

DateModified - datetime, defaults to getdate().   You can update this to the current date with each modification.  If you want to keep a history of all modifications, then it would be better to have a history table.  For now, this keeps it simple.

CreatedBy - Typically an integer or whatever field you can use to link to an ID of who created the record.

ModifiedBy - Typically an integer or whatever field you can use to link to an ID of who last modified the record.

ContactStatus- Can be an integer defaults to 1.  If the customer ever goes inactive, I find people want to delete and that can create problems later. I prefer to set the status to 0 and program accordingly where needed.

LeadSource - in order to track where the contact came from.  This can be plain text or an ID that links to another table of LeadSource options.  I will typically have two of these fields. One for the source (Radio, TV, WebLead), then a detail field for a specific campaign or detailed source like WebLead :Experts-Exchange.

EmailOptIn  - You are collecting email and may wish to send information to your contacts.  This can be an integer where 0 means opt out of all email communications, 1 means only send account related (invoices) and 2 is send account and marketing related.  Y

 SMSOptIn - I am working with clients on sending sms alerts.
asp_net2Author Commented:
@Scott, very good advice.. Thank you, I will definitely implement that.
asp_net2Author Commented:
@Scott,

For the DateCreated and DateModified should those Data Types be DateTime? I'm using SQL Server and wasn't sure if I need to add anything else to DateTime. I would like the format to be DATETIME - format: YYYY-MM-DD HH:MI:SS for DateCreated and DateModified. DateModified will only be updated when I need to update that customer. DateCreated will never change.
Scott FellDeveloper & EE ModeratorCommented:
> DATETIME - format: YYYY-MM-DD HH:MI:SS

That looks good. it goes through YYYY-MM-DD HH:MI:SS.9999

I keep the fraction of a second because it can help in troubleshooting later for something like duplicates getting created.  For display, I will just use YYYY-MM-DD HH:MI:SS
asp_net2Author Commented:
Yes, but in SQL Server it's asking for Data Type which is either "DateTime" or "DateTime2". But is there anything that I need to add to that column to give me the format above that i need?
Jim HornSQL Server Data DudeCommented:
The question was answered as asked.
asp_net2Author Commented:
I apologize for the late reply. I have been away for awhile.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.