Need advice for database structure (MS SQL / MySQL)

Question

Preferably MS SQL, I want to make a switch from MySql.

So, I have this wonderful client that has 4 excel files. Each Excel file represents a Product Range .

Inside each Excel file, there are 3 to 8 sheets. Each Sheet represents a Type of Product inside this Product Range .

Each sheet contains the following columns:

PartNo , Description , QTY , Price1 , Price2 , Price3 , Price4 ...

(There have never been and should not be more than 8 price columns.)

Each sheet can contain from 5 to 5000 lines.

Now the problem that I am facing now does not know what would be the best way to set up my new database.

I currently have an existing MySQL database, each of which represents a table. It! (He needed to be quickly "from there", therefore, the lack of time invested in creating the proper format / structure for the database.)

I recently discovered that I am more competent in using MSSQL databases, so I want to make this switch, and the second reason is the main reason, because I want to restructure the database so that I can do things to simplify management and simplify setting up database searches with my site.

I don’t worry at all about how to insert everything into the database, since in my free time over the past year I wrote an application that analyzes these Excel files, extracts the sheets and inserts them into the database, with additional settings. I am worried about how , I have to actually configure this new DB.

What would be the best way to do this, given the details above?

Any help is generally much appreciated. Thanks!

Update:

About pricing columns (example), there is some information on why there are several price columns in each sheet .:

Price column 1 may be galvanized Unit price, price Column 2 may be galvanized box Price, column 3 prices may be the price of a box made of stainless steel GR304, and column 4 may be stainless steel GR316. These price columns are different for each product range, however, some products in the product range may also contain some of the same price columns. That is why it was so easy to have each product in a separate table.

Answer 1

I assume you are looking for database design advice, right?

I will never create a database with every sheet representing the table. I will have one table for a conceptual entity, and in your case the Product is obvious.

So, a table called "Products" with the columns you mentioned above. To place a product type, I would just have a type column that indicates which type of product any particular product belongs to. In the end, I used the enum type in .Net to specify the supported types.

Well, that was my five cents. Hope this helps.

Answer 2

A simple solution would be to split it into 3 tables. ProductRange and ProductType are associated with the Product table through foreign key relationships.

 ProductRange Id RangeName // Plus any other columns you need. eg description, startdate etc ProductType Id ProductTypeName Product Id ProductRangeId ProductTypeId PartNo Description Qty Price1 // etc 

If you need more flexibility in price, you must create another Price table with a many-for-many table between Product and Price .

 Price Id Description Price ProductPrice PriceId ProductId 

In this case, the Product table does not contain price columns. But now you can add as many prices as you need, and each Product can have any number of prices.

Answer 3

Having multiple columns for a price seems a little strange to me if Price1 doesn't mean “Business Rate” and Price2 means “Consumer Rate” or something like that. If different prices are for different functions of the same product, you may need to split them into another table. For instance:

Product Table : KeyTable, ProductDescription ...

Price Chart : KeyTable, KeyProduct, PriceType, Price

Alternatively, depending on how prices are calculated, you can use various other prices at the same base price. For example, if the price of a business rate is to add 5% to the base price, and the consumer price should add 20% to the base price, they probably should not be stored in the database, as they can be easily calculated when they are needed.