Database Design where some products are T-shirts with different sizes

Hello,

I'm wondering what would be the best approach to designing a database that will have different products one of them being T-Shirts of different sizes... for example 1 t-shirt design might only have 2 available sizes while another may have 4. I'm kinda stumped on how to approach this cuz there is multiple products like CD's, DVD's, Magazines etc which is pretty straight forward, but the T-shirts have this "variable" to it.

What i'm really wondering is should i have 1 main "Products" Table or should i have a separate table for the t-shirts?
Should there be a column for each available size?

Currently my database has a "products table" that has foreign keys to "Product Type", "Artists", "Genre"

The database is basically for a record company

If anyone has designed a database similar to this i'd love any insight or even possibly to see a database diagram

Thanks

Here is my suggestion:

Table: Products
Id - PK
Name
Description
Price

Table: Attributes
Id - PK
Name
Label

Table: AttributeValues
Id - PK
AttributeId - FK
Value

Table: ProductAttributes
Id - PK
ProductId - FK
AttributeId - FK

In your order line, you will have to store the ProductID And then in a child table store the zero to many associated ProductAttributeId's. Here is an example

Table: Products
Id - 1
Name - Men's ABC Polo
Description - Nice Shirt
Price - 20.00
Id - 2
Name - Women's ABC Polo
Description - Nice Shirt
Price - 25.00

Table: Attributes
Id - 100
Name - Men's Sizes
Label - Size
Id - 101
Name - Women's Sizes
Label - Size

Table: AttributeValues
Id - 201
AttributeId - 101
Value - Small
Id - 202
AttributeId - 101
Value - Medium
Id - 203
AttributeId - 101
Value - Large
Id - 204
AttributeId - 101
Value - Extar Large
Id - 205
AttributeId - 102
Value - Extra Small
Id - 206
AttributeId - 102
Value - Small
Id - 207
AttributeId - 102
Value - Medium

Table: ProductAttributes
Id - 300
ProductId - 1
AttributeId - 101
Id - 301
ProductId - 2
AttributeId - 102

The attrubutes are reuseable accross multiple products and the use of attributes is optional. When you take the order you would store the product id of 1 for a Man's polo with the attribute value in another table relating to the line item. You put this in another table so you can have potentially multiple attribute values.

Make sense? Just one way of many... this was off the top of my head and it is late.

Database creation with SMO in SSE

Hi there.

I tried finding any info regarding this approach and I can't find any, so here I am (again!).

I'm trying to create a database using SMO on a freshly installed SQL Server Express (not going through the advanced install, not going through the surface area configuration, so a lot of things are "wrong").

I get an error

- use SMO to try and create a database; this is the pseudo-code combo used:

1. create new Smo.Database object;

2. create new primary file group and data file for that database (using Smo.FileGroup and Smo.DataFile); add them to the database.

3. invoke Create(). catch exception with the following details:

Exception details:

Microsoft.SqlServer.Management.Smo.FailedOperationException was caught
HelpLink="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476"
Message="Create failed for Database 'NewDatabase'. "
Source="Microsoft.SqlServer.Smo"
Operation="Create"
StackTrace:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
at Microsoft.SqlServer.Management.Smo.Database.Create()
at Penta.Core.Library.Sql.Classes.Penta_SqlDatabase.Create() in C:\Documents and Settings\[...]

InnerException:

{"Directory lookup for the file \"C:\\Documents and Settings\\[...]\\NewDatabase.mdf\" failed
with the operating system error 5(Access is denied.).\r\nCREATE DATABASE failed. Some file names listed could not be created. Check related
errors."}

Seems like user rights error.

Long story short, the only way the creation step works is if I change the Log on for the SQL Express service from the default Network Service to Local System (either through code or not).

Now, I know LocalSystem is not a wise idea, but it seems to be the only way.

I know it's probably something stupid, but what am I missing?

Thanks you in advance

Hi Calin,

You're right, it's a permissions error. Network Service does not have permissions to read and write files in your User Profile directorys (i.e. the stuff under %My Documents%). This is by design as SQL Express runs as Network Service is the default Service Account for SQL Express. Local System does have permissions to your user profile direcotries, so changing the Service Account results in success. You could have also...

Changed the Service Account to any other account that has permissions to the directory where you want to create your files.|||

Hi, Mike, and thank you for your reply. Your comments make perfect sense, so I'll try and circumvent the problem.

Theoretically, my app would be ran from client machines, with the database on a file server. However, it can also be used locally (small company, single user, etc.). Since more than one user can access the same data in the same time, I don't think User Instances would be a good choice for me.

I do plan on shipping SQL Express with the app, however the users can choose to get SQL Server and use it instead, so using User Instances is not an answer because of that, either.

Of all, it seems the best way would be to change the "default" folder where that data is created, the issue is that the user can choose where they want the database file(s) to be created, so they may specify My Documents :-) In the case of a real shop with someone who knows what they're doing this will most likely not happen, however, the application is suppose to be easily accessible for all users, regardless of their abilities.

Thank you again for your help,