Preferrably MS SQL, I wish to result in the transition from MySql.

And So I have this awesome customer that has 4 Stand out Files. Each Stand out file signifies a Product Range.

Within each Stand out File, you will find between three to eight Sheets. Each Sheet representing a Type of Product within that Product Range.

Each Sheet, consists of the next Posts:

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

(There's never been, and will not be anymore than 8 Cost Posts.)

Each Sheet could have from about 5 to 5000 rows.

Now, the issue I'm facing now, isn't knowing which may be the easiest method to start establishing my new Database.

Generate an income presently have our existing MySQL Database is each Sheet signifies a Table. There you have it! (It needed to be placed "available" rapidly, hence the possible lack of time invested into establishing an effective format/structure for that DB.)

I have lately found that i'm a lot more competent using MSSQL databases, so I wish to result in the switch, and another factor to consider, the primary reason, happens because I wish to restructure the Database in order to make things simpler to handle, making it simpler to create Database searches from the site.

I am not worried whatsoever concerning how to place everything in to the database, as with my free time for that this past year I have written an application that parses these Stand out files, extracts the sheets, and card inserts them in to the DB, with optional configurations. I'm concerned about how I ought to really setup this new DB.

An amount be the easiest method to build a storage shed because of the above particulars?

Any help whatsoever is greatly appreciated. Thanks!


Concerning the Prices Posts (Example), just a little information on why you will find several cost posts in every sheet.:

Cost column 1 might be Galvanised Unit Cost, Cost Column 2 might be Galvanised Box Cost, Cost Column 3 might be Stainless GR304 Box Cost, while column 4 might be Stainless GR316 Unit Cost. These cost posts will vary for every Range Of Products, however, some items inside a Range Of Products could also contain a few of the same Cost Posts. For this reason it had been very easy to possess each product like a separate table.

I suppose that you are seeking suggestions about the style of the database, right?

I'd never design the database with every sheet representing a table. I'd have one table per conceptual entity, as well as in your situation Method is the apparent one.

So a table known as Items, using the posts you pointed out above. To accomodate the merchandise Type, I'd only have a kind column that signifies which Product Type any particular Product goes to. I'd eventually make use of an enum key in .Internet to specify the various types that's supported.

Well, which was my five cents. Hope it assisted.

An easy solution is always to split it into 3 tables. ProductRange and ProductType are from the Product table with the foreign key associations.

   // Plus any other columns you need. eg description, startdate etc


   // etc

Should you needed more versatility round the cost, you'd create another Price table having a many-to-many joining table between your Product and Price tables.



Your Product table wouldn't contain any cost posts within this situation. However, you can now add as numerous Cost Types since you need and every Product might have a variety of Prices.