Basic SQL Server Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Basic SQL Server Help

Author

Topic

lakers34kb
Starting Member

15 Posts

Posted - 2010-01-15 : 14:50:47

The owners have shared with you some minimum design requirements for the Inventory tracking database:
I'm having trouble with #3, I have the Product table set up as follows:

SKU nchar(10)
Name nvarchar(50)
Description text
Quanity int

My question is what do I got about doing to make sure "the system must be able to deactivate a product without deleting it from the system."

Business Rules:

1. The system must uniquely identify every product in the company. Every product will have a unique SKU assigned to it which is ten characters and digits in length. It is believed this product SKU would only change on very rare occasions. Each product will also have a name and description.
2. The system must track the current quantity stored in the warehouse of each product.
3. The system must be able to deactivate a product without deleting it from the system. The date each product was introduced at the company must also be stored.

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 14:53:50

How about adding a column that indicates whether the product is Active, or not?

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 14:55:06

You'll need to add a column for the last bit of #3 too ("The date each product was introduced at the company must also be stored.")

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 15:02:46

SKU nchar(10)
Name nvarchar(50)
Description text
Quanity int
Product_status tinyint
Product_introdate datetime

No, you're never too old to Yak'n'Roll if you're too young to die.

lakers34kb
Starting Member

15 Posts

Posted - 2010-01-15 : 15:15:37

If I use tinyint for the status, how is that going to tell me if it's active or not?

I know in Access there is an option to have a Yes/No check field... is there anything like that in SQL?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 15:16:30

also don't use text data type. find out the required length for the description field and use varchar(n)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 15:19:02

i'd use a bit field for active/inactive

SKU nchar(10)
Name nvarchar(50)
Description ~~text~~
Quanity int
Active bit
Product_introdate datetime

0 = inactive
1 = active

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 15:49:35

I use tinyint for that because it is possible that in the future there is a need for more than active/inactive...
The bit field has nothing to do with behaviour in ACCESS as far as know.

If I use tinyint for the status, how is that going to tell me if it's active or not?
Your application can set the field to a value that you want it to have for active or inactive.
What is the problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 15:54:09

bit is the same as yes/no in access.

i sometimes do what Fred is talking about too.

you may have several statuses:
1. Pending
2. Approved
3. Active
4. Suspended
5. Inactive
etc.

Just make sure you create a lookup table with the values and their descriptions, and a foreign key to that table if you do it that way.

lakers34kb
Starting Member

15 Posts

Posted - 2010-01-15 : 17:22:06

-------------------------------------------------------------------
Business Rules:

1. The system must uniquely identify every product in the company. Every product will have a unique SKU assigned to it which is ten characters and digits in length. It is believed this product SKU would only change on very rare occasions. Each product will also have a name and description.
2. The system must track the current quantity stored in the warehouse of each product.
3. The system must be able to deactivate a product without deleting it from the system. The date each product was introduced at the company must also be stored.
4. The product’s current selling price must also be stored. This value will never be above $1000.00 per product.
5. For each product, a minimum and maximum level must be stored. This minimum level represents a quantity of inventory that should always be on hand and indicates when to order more product; the maximum level represents the quantity that should not be exceeded in the warehouse.
6. The system must allow for each product to be supplied by multiple vendors. You should also provide a way to indicate which vendor is preferred when multiple vendors supply the same product.
7. The vendor info stored should include the company name, contact name, address, telephone number, fax and email address. Not every vendor has a fax number. Like a product, a vendor must be able to be deactivated without deleting it from the system.
8. Every product can be assigned to only one category.
9. For the category, only a name and a brief description needs stored. Examples of categories include food, toys, and medical.

-------------------------------------------------------------------

Thanks for your help... I think I got it completed now

SKU nchar(10)
Name nvarchar(50)
Description text
Quanity int
Active bit
Date_Introduced datetime
Current_Price smallmoney
MinQuanity int
MaxQuanity int
PreferredVendor tinyint
ProductCategories nvarchar(50)

CompanyName nvarchar(50)
ContactName nvarchar(50)
Address nvarchar(100)
TelphoneNumber int
Fax int
Email nvarchar(100)
Active bit
SKU nchar(10)

CategoryName nvarchar(50)
Description text

ProductCategories nvarchar(50)
CategoryName nvarchar(50)

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 04:12:14

"6. The system must allow for each product to be supplied by multiple vendors. You should also provide a way to indicate which vendor is preferred when multiple vendors supply the same product."

You have created a column

PreferredVendor tinyint

for this? I don't think this will work (as you have it set up, but its the right idea). How do you envisage this working?

"8. Every product can be assigned to only one category."

I think what you have created is over complicated to solve this part.

"TelphoneNumber int
Fax int"

Note that you cannot store leading zeros (or any spaces/punctuation etc.) in an INT. Personally we use varchar for phone numbers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources