Schema for SQL database of guitars

Question

I just want to ask you guys if the database design that i made for my asp.net website is correct or not. So basically, I created a database for the brands of my guitar products, which is like this:

Database for Brands

Code for Database Brands:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[guitarBrands](
[id] [int] NOT NULL,
[type] [varchar](50) NOT NULL,
[name] [varchar](50) NOT NULL,
[image] [varchar](255) 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

INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (1, 
N'Guitar', N'Ibanez', N'../Images/Guitar Brands/ibanezLogo.jpg')
INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (2, 
N'Guitar', N'Fender', N'../Images/Guitar Brands/fenderLogo.jpg')
INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (3, 
N'Guitar', N'Gibson', N'../Images/Guitar Brands/gibsonLogo.jpg')
INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (4, 
N'Guitar', N'PRS', N'../Images/Guitar Brands/prsLogo.jpg')
INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (5, 
N'Guitar', N'Musicman', N'../Images/Guitar Brands/ernieballLogo.jpg')
INSERT [dbo].[guitarBrands] ([id], [type], [name], [image]) VALUES (6, 
N'Guitar', N'ESP', N'../Images/Guitar Brands/espLogo.jpg')

and i also have another database for the guitar models within that brand.

Database for Guitar models(I hope the image is clear)

And here is also the code for database guitar models:

 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,
 [itemimage1] [varchar](255) NULL,
 [itemimage2] [varchar](255) NULL,
 [description] [text] NOT NULL,
 [necktype] [varchar](100) NOT NULL,
 [body] [varchar](100) NOT NULL,
 [fretboard] [varchar](100) NOT NULL,
 [fret] [varchar](50) NOT NULL,
 [bridge] [varchar](100) NOT NULL,
 [neckpickup] [varchar](100) NOT NULL,
 [bridgepickup] [varchar](100) NOT NULL,
 [hardwarecolor] [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], 
 [itemimage1], [itemimage2], [description], [necktype], [body], [fretboard], 
 [fret], [bridge], [neckpickup], [bridgepickup], [hardwarecolor]) 
 VALUES (1, N'Guitar', N'Ibanez', N'ARZ307', 9000.00, N'../Images/Guitar 
 Brands/Guitar Items/ibanezARZ307.jpg', N'../Images/Guitar Brands/Guitar 
 Items/ibanezARZ307StandingPosition.png', N'Its a Les-Paulish shaped guitar 
 with a black body and head stock. This is a seven string guitar tuned 
 BEADGBe from low to high. The grade and quality of this guitar is 
 exceptional compared to some other brands out there.', N'ARZ Mahogany SetIn 
 Neck Smooth Heel', N'Maple Top/Mahogany Body', N'Bound Rosewood Fretboard 
 W/White Block Inlay', N'24 Medium Frets', N'ART-1 Bridge', N'Classic Elite 
 Neck Pickup Passive/Ceramic', N'Classic Elite Bridge Pickup 
 Passive/Ceramic', N'Chrome')

 INSERT [dbo].[guitarItems] ([id], [type], [brand], [model], [price], 
 [itemimage1], [itemimage2],[description], [necktype], [body], [fretboard], 
 [fret], [bridge], [neckpickup], [bridgepickup], [hardwarecolor]) VALUES (2, 
 N'Guitar',N'Ibanez', N'DT420TCR', 11000.00, N'../Images/Guitar 
 Brands/Guitar Items/ibanezDT420TCR.jpg', N'../Images/Guitar Brands/Guitar 
 Items/ibanezDT420TCRStandingPosition.png', N'The Destroyer Series DT420 is 
 made from mahogany with a transparent cherry sunburst gloss finish. The 
 single cutaway solid body harks back to the original 1975 design, 
 delivering maximum resonance and sustain.', N'Destroyer Mahogany Neck', 
 N'Mahogany', N'Bound Rosewood Fingerboard with Acrylic and Abalone Block 
 Inlay', N'22 Jumbo Frets', N'Full Tune III', N'Classic Elite 
 Humbucker,Passive/Ceramic', N'Classic Elite Humbucker, Passive/Ceramic', 
 N'Chrome')

 INSERT [dbo].[guitarItems] ([id], [type], [brand],[model], [price], 
 [itemimage1], [itemimage2], [description], [necktype], [body], [fretboard], 
 [fret], [bridge], [neckpickup], [bridgepickup], [hardwarecolor]) VALUES (3, 
 N'Guitar', N'Ibanez', N'JBM100', 18000.00, N'../Images/Guitar Brands/Guitar 
 Items/ibanezJBM100.jpg', N'../Images/Guitar Brands/Guitar 
 Items/ibanezJBM100StandingPosition.png', N'Signature model guitar from Jake 
 Bowen of Periphery. Due to their heavy progressive sound, He decided to 
 move the boundaries of guitar design and performance with his new signature 
 guitar. This guitar is the main definition of tight and aggressive.', N'JBM 
 3pc Maple/Bubinga neck', N'Maple top/Mahogany body', N'Bound Ebony 
 fretboard w/JBM special inlay on 12th fret', N'24 Jumbo frets w/Prestige 
 fret edge treatment', N'   Lo-Pro Edge tremolo bridge', N'DiMarzio® Titan™ 
 (H) neck pickup (Passive/Ceramic)', N'DiMarzio® Titan™  (H) bridge pickup 
 (Passive/Ceramic)', N'Black (Gold machineheads)')

 SET IDENTITY_INSERT [dbo].[guitarItems] OFF

actually there are three more columns but it doesn't fit in the image but you can easily identify it in the code. Also i did not include all the code for guitar models because it is too many to show it here.

Please kindly advice on what is the best approach to creating a database design based on the details given above. By the way, this is just for a personal project and will be used for learning purposes only. But I might use this in my portfolio if all goes well.

Answer 1

If you're wanting to practice doing RDB correctly, I'd suggest getting very used to ensuring your schema are fully normalized.

In this case, you half-way did it. You have your brands table and a guitars table but the relationship between the two is based on a column other than the brands table's PK.

Change your guitarItems.brand varchar to an int and add the foreign key relation on guitarBrands.id . That way, if the varchar ever changes, your relational model isn't borked. Your case might not be a good example, except in the rare instance of a brand changing names. But it's much more common for other strings that can change frequently (or for simple typo-protection).

The same goes for the guitar type. Make a new table for types with an AI PK and the type's display name. Then normalize your guitars table to use that PK.

That could be extended as far as you see fit. Other candidates would be necktype, body, fretboard, etc. Any place you see a lot of repeating or standardized data is a good candidate for being broken off into a separate table. Not always, but it's a good first indication.

On top of this preserving your relational model in the case of data revisions, it can significantly speed up lookup times. For instance, if you can query WHERE foreign_key_int = 2 rather than WHERE foreign_key_varchar = 'Some really long name' you can see real benefits. Especially if you're doing more complex joins or large IN queries.

This also has the benefit of saving you space with each record. If you're fully normalized, you're only storing a handful of bytes for the foreign keys, rather than duplicating long strings over and over. For larger databases, that can be the difference between the database flying or grinding to a halt.

---

Aside from the normalization, I'd also recommend you get in the habit of not storing full file paths in your database. File system hierarchies change all the time and you never know what the working directory is of whatever is reading your database records.

If you store the images in \path\to\images\logo.png, store logo.png in your column and then define in code how to get to that image. The code executing that query is the only one that knows what its working directory is. So decouple your data layer and your business/presentation layers by letting it build out that path at run-time.

As well, if your hierarchy ever changes (say, you want to store all your images under a new folder or on a cloud host like AWS), you only have to change your one or two functions in the code base. If you stored the path in the DB, you'd have to go messing with the data by updating every single record.