How to create the best database design in MSSQL?

Question

1.00/5 (2 votes)

See more:

Hi! I'm learning all about databases now. I just want to ask if it's okay to create a database table, where there are a total of 17 fields. And instead, should I create a separate table for it? or is it better as it is? Kindly advice a better design for this or if i should try implementing relational database to it. I have included the sql code below.

What I have tried:

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[guitarItems](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[type] [varchar](50) NOT NULL,
	[brand] [varchar](50) NOT NULL,
	[model] [varchar](50) NOT NULL,
	[price] [float] NOT NULL,
	[image logo] [varchar](255) NULL,
	[item image1] [varchar](255) NULL,
	[item image2] [varchar](255) NULL,
	[description] [text] NOT NULL,
	[neck type] [varchar](100) NOT NULL,
	[body] [varchar](100) NOT NULL,
	[fretboard] [varchar](100) NOT NULL,
	[fret] [varchar](50) NOT NULL,
	[bridge] [varchar](100) NOT NULL,
	[neck pickup] [varchar](100) NOT NULL,
	[bridge pickup] [varchar](100) NOT NULL,
	[hardware color] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[guitarItems] ON
INSERT [dbo].[guitarItems] ([id], [type], [brand], [model], [price], [image logo], [item image1], [item image2], [description], [neck type], [body], [fretboard], [fret], [bridge], [neck pickup], [bridge pickup], [hardware color]) VALUES (1, N'Guitar', N'ESP', N'Alexi 600', 17000.00, N'../Images/guitarLogo.jpg, N'../Images/Guitar Brands/ESP Guitars/espAlexi600.jpg', N'../Images/Guitar Brands/ESP Guitars/espAlexi600StandingPosition.jpg',
			N' Hardcore instrument that is only fitting for the darkest of music.', N'Maple Neck', N'Mahogany Body',
			N'Maple Fretboard', N'24 Frets', N'Regular Control',N'Single Coil', N'Single Coil', N'White')
INSERT [dbo].[guitarItems] ([id], [type], [brand],[model], [price], [image logo], [item image1], [item image2], [description], [neck type], [body], [fretboard], [fret], [bridge], [neck pickup], [bridge pickup], [hardware color]) VALUES (2, N'Guitar, N'ESP', N'EC 50', 14000.00, N'../Images/guitarLogo2.jpg, N'../Images/Guitar Brands/ESP Guitars/espEC50.jpg', N'../Images/Guitar Brands/ESP Guitars/espEC50StandingPosition.jpg',
			N' Hardcore instrument that is only fitting for the darkest of music.', N'Maple Neck', N'Mahogany Body',
			N'Maple Fretboard', N'24 Frets', N'Regular Control',N'Single Coil', N'Single Coil', N'Grey and Black')


SET IDENTITY_INSERT [dbo].[guitarItems] OFF

Posted 28-Apr-17 4:05am

BebeSaiyan

Updated 28-Apr-17 4:42am

Add a Solution

Comments

PIEBALDconsult 28-Apr-17 10:17am

Please do not put SPACEs in names.
Try not to use keywords as names.
Don't pluralize names.
Identities are filth.
Why specify your string values with N when the columns are not N?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-04-28T04:42:00

Solution 1

BebeSaiyan said:
I just want to ask if it's okay to create a database table, where there are a total of 17 fields.

Yes.

BebeSaiyan said:
And instead, should I create a separate table for it? or is it better as it is?

It's better as it is. A separate table for "it" (whatever "it" is) is unlikely to be a good idea.

BebeSaiyan said:
if i should try implementing relational database to it

I'm not sure that you know what is meant by a Relational Database. I think what you really mean is "Should I apply Normalization to the database". The answer to that question is invariably "Yes". See The Basics of Database Normalization[^]
See also the comments from @PIEBALDconsult

PIEBALDconsult said:
Please do not put SPACEs in names.
Try not to use keywords as names.
Don't pluralize names.
~~Identities are filth.~~
Why specify your string values with N when the columns are not N?

I have struck out the comment on Identity columns as that was a statement of opinion and opinions on the use of Identity columns vary from developer to developer. I suggest you read this article however - Aaron Bertrand : Bad habits to kick : putting an IDENTITY column on every table[^]

Posted 28-Apr-17 4:42am

CHill60

Comments

BebeSaiyan 28-Apr-17 11:39am

Thanks for this. Highly appreciate it.

[no name] 29-Apr-17 5:32am

I can imagine that PIEBALDconsult means it would be better/more flexible using sequences instead of IDENTITY.

CHill60 29-Apr-17 8:10am

Good point if the OP has SQL Server 2012 or later.
For anyone else coming across this here's an article on that subject SQL Server: Sequences (Autonumber)[^]

[no name] 29-Apr-17 8:22am

A second very good link ... but no chance for me to upvote again

CHill60 29-Apr-17 8:24am

That's ok :-)
I'm told I'm too didactic sometimes, but I just can't stop myself rounding things off :laugh:

[no name] 29-Apr-17 8:45am

"I'm too didactic"... :-)
Please let me know your opinion concerning "Don't pluralize names".
I'm always fighting on this with myself, but for me table names in plural are in 98% more intuitive. But maybe this is also because I'm not native english.
Thank you in advance.

[Edit]
For me a table "ORDERS" sounds good.
But always I fight if we come to a 1:N (N:M) relations...
Should I Name now the Detail table "ORDERS_ITEMS" or "ODRER_ITEMS"? In this case "ODRER_ITEMS" sounds better for me :headache: for nothing? :laugh:

CHill60 29-Apr-17 9:40am

To be honest I don't have a strong opinion on it - Microsoft sample databases aren't consistent on the convention either - Northwind has a table [Order Details] (not just plural but a space too!) but AdventureWorks has [SalesOrderDetail] - singlular (and no spaces - I do agree with that bit!). There is a really interesting set of arguments both for and against here ...sql - Table Naming Dilemma: Singular vs. Plural Names - Stack Overflow[^].
I do tend towards using the singular form for my own tables - probably more through laziness :-)

[no name] 29-Apr-17 9:55am

Thank you for this.
That calms me, that even professionals can not just justify this so easy (Google translated :( )
Bruno

BebeSaiyan 30-Apr-17 8:50am

awesome additional info!