Lots of fields, or lots of tables?

[Infinite]

Hello! I have currently 5 tables, and each one is for sales. They are as follows,

tblAmazonOrders
tblEcwidOrders
tblVendorsMart
tblEarthOutdoors
tblShowSales

Now, all of those are are just tables for sales, what im trying to figure out, is it okay for me to have 5 different tables, all for the same thing, or 1 table, with lots of fields. The reason I have to have lots of fields is that in the Amazon and Ecwid, all the fields have different names. For instance, in the tblAmazonOrders, the item description, is just that. Description. For the item name in tblEcwidOrders, the description is Name. And that is just ONE of the many different field names, that all mean the same thing. So, should I have one field for all sales? Or, should I have 5? I know I shouldn't have 5, so what do I do now? Maybe have the data in a append query? And then have it delete all the data after it appends the query? Would something like that work? Thanks for all your answers!

 

[spikepl]

Imagine making your db, with a table for each outlet. All the queries, daily/monthly sales reports etc.: in each you'd have to refer to each of the tables.

And then you get one more outlet. What do you do?

Now imagine the lot being in one table. What happens if you add one more outlet?

 

[Infinite]

Now imagine the lot being in one table. What happens if you add one more outlet?

I just add some more fields to the main table, instead of creating a new table, and then having to reference to it.

So just 1 giant table is good?

 

[The_Doc_Man]

My thought is one big table and then queries that you can use with "AS xxxx" in the various fields so that you can "map" you common fields to the specific fields needed for each different order subset. This DOES require you to do a little work on how the main table is laid out to contain everything you need, but on the other hand, when you have to do aggregate reports that cross subset lines, everything is in one place.

Look at it this way: If you have X tables with Y fields per table, you have X * Y fields to maintain. If you have one table with Z fields, you have Z fields to maintain and X queries to create for insertion, deletion, selection, updating, etc. But queries are one-liners and take up very little space. The overhead of adding one more table with its indexes and rows and fields can't be easier than writing a few more one-liner queries. Particularly if you have to build a new set of forms and reports for the new subset. Whereas if you write the main table and one set of "smart" forms/reports, the maintenance is again far cheaper. Not to mention that you add fewer objects to the database. I could probably think about it and give you some theoretical reasons why you should also choose one big table vs. a bunch of subset tables, but the pragmatic issues of maintenance cost is what really steps out as a major factor.

 

[spikepl]

The storage of data in a DB is not dependent on what things are called outside.

I think you got confused between the naming variations of your various outlets and the contents. The contents are all the same - they just have different names. So the same stuff you store in the same columns. You use the different external names when needed.

 

[Infinite]

The contents are all the same - they just have different names.

So...what does that mean?

I...er, not sure that you mean. I should have 1 table, with lots of fields, but...I should...Confused here, sorry.

 

[spikepl]

So what are these "lots of fields" ?

If Amazon calls something ITEM but EarthOutdoors calls the same entity Name, it is still the same entity, not requiring a separate column. So you have a column eg called TheNameOfTheThing . Stuff form Amazon gets ITEM stuffed in there. Stuff from EarthOutdoors gets info from what they call Name stuffed into TheNameOfTheThing .

I am not going to spend any more effort on this unless you show which columns you decided to have. Otherwise we are just supplying digital arm-waving - a rather pointless exercise.

A hint: Don't get stuck into supplying stories but show what you've got - structure, queries, code, whatever, whenever you need help. It makes your thinking so much more clear for outsiders, and makes it so much easier to help sensibly.

 

[plog]

What spike is trying to guide you to is that you store values in a field in a table. You don't store values in a field or table name.

If you were working on a personell database, you wouldn't have tables like this:

Personnell_Male
Personnell_Female

You would have just a Personnell table. And in that table you wouldn't have fields like this:

FirstName
LastName
Male
Female

It would have this:

FirstName
LastName
Gender

Within the Gender field is where you would store the Female or Male value. Not in a table or field name.

So to answer your initial question: Neither, both your proposed solutions are wrong. You would create a field in your table to store the value of the sales type (e.g. Amazon, EarthOutdoors, Show, etc.).

 

[Infinite]

Ok, the thing that I need, is I can have just a table named item, and in that table, I can have the all the items in it, BUT, I want to be able to import all of my items into it. Not just copy and paste it.

But, I am seeing that that is not going to be a viable solution for what I need. So, I guess I should just stick with one big table, and just copy and past what I need into the one field, for that item, zip code, etc, etc.

 

[vbaInet]

Can I just say that I once saw Infinite's db and s/he actually needs help with all his tables, so if you guys don't mind helping that would be great. It's now up to Infinite to provide a legible screenshot of all his tables and describe the project.