Database Design Question

[dmuroff]

Good day,

I am in the process of creating a database in Access.

I have just over 10,000 products with just about 20 fields of data required.

These products can belong to 15 product types.

Also, I wish to store competitor product information for each product.

Should I create one table with the 10,000 products?
Or should I create 15 tables? One for every product type?
Thanks!

 

[zemp]

I would have three tables. One for products, one for product types and a third look up table that just relates a product and product type. The third table will track which different product types each product belongs to and will only contain foreign keys from each table.

zemp

 

[dmuroff]

The competitor info can be stored in the products table?

 

[dmuroff]

Oops
Just realized that every product type and its associated products may have different fields of data.

Guess I will have to make seperate tables.

 

[dmuroff]

Let me try to be a little more specific..

I have 10,000 products.
There are 40 product categories.
Some fields are the same for all products. (I.E. description, price).
Some fields are unique to the category of product.

I am thinking this:

1. Have a table called products with all 10,000 products including description, price, producttypeID and productID.

2. Have a table with producttypeID and ProducttypeDescription.

3. Have 40 tables with ProductID and fields that are unique to that category of product.

Is this the right way to go about it?

Thanks in advance.

 

[bzsurf03]

Is it proper db design to have one product table with all the possible data fields (even the 'unique' ones) some of which you know will be blank depending on the product type?

 

[harebrain]

Is it proper db design to have one product table with all the possible data fields (even the 'unique' ones) some of which you know will be blank depending on the product type?

First, change the word "blank" to "null." Then the answer depends on whether you subscribe to the school of thought that accepts null as a legitimate place-holder where a value ought to be, or to the school that rejects null outright. The second school is more current, the first seems more practical in this case.

 

[ceh4702]

One way to limit the size of the table is to use codes. You might have categories, Type, or be using the Product group codes like the North American Industry Classification System (NAICS) Codes, which are standardized codes and classification of products. Probably a lot of different marketing research firms use these codes. You can store the codes in separate tables.

When you use codes you can more easily identify and standardize the products and their names. By storing them in a lookup table you can acutally develop data entry forms for which you have drop-down boxes to select the categories from.

If you have all of the manufacturers in a separate table you could include the address and location. Or if you prefer maybe a vendor is more apporpriate than manufacturer. Either way you can assign manufacturer or vendor numbers to associate with the names. In the Manuf or vendor table you can put address phone numbers or contact info as well as last contact date. A date is important to know if you want to drop products out of the table after x days or years of inactivity.

If you are keeping a product inventory you may want a product order table showing prices and quantities. You may need to know which order they are on and what line they are on. Accountants may want to associate orders/purchase orders with their accounting practices. The same would go for items you are selling. You may need to make shipping and order forms and record that activity along with the names of the buyers and their contact information.

If you do not like my post feel free to point out your opinion or my errors.

 

[r937]

the school that rejects nulls outright is more current???

in that case, what does "current" mean?

there's nothing wrong with nulls (pardon the pun)

;-)



rudy

http://r937.com/

SQL Consulting

 

[harebrain]

r937:

You might want to explore the site,

http://www.dbdebunk.com

for this info. The primary work on the site is contributed by C.J. Date and Fabian Pascal, both heavy-hitters in the field. (Massive understatement.)

The work I hoped you'd see, 09/17/04 #8: THE FINAL NULL IN THE COFFIN, Pascal, is no longer free. You can, however, search the site as far back as 2001 for germane material. Or buy one of their books, which they also promote.

What it boils down to is that ambiguities and contradictions are created wherever null is allowed in SQL databases. Does null mean "unknown" or "not applicable?" (Which would be a good question for the subject database of this thread.) SQL provides no tools for handling the distinction, which then forces cumbersome logic to the application level.

Let me end by answering your question about the meaning of "current": Date and Pascal have been at this for years, but why not peg "current" as meaning "Sep 2004" (see citation above) when they claim to have had the last word on the matter.

 

[r937]

harebrain, thanks, i know all about those guys

the world is divided into two types of people: those who can stomach fabian's colossal rudeness and arrogance, and those who can't

three guesses as to which camp i'm in



i repeat: there's nothing wrong with nulls in the real world



rudy

http://r937.com/

SQL Consulting

 

[harebrain]

there's nothing wrong with nulls in the real world

Nulls don't exist in the real world, only in databases. ;-)

 

[r937]

heh, nice one

except i can offer several examples:

1. when did you stop beating your wife?

2. how many votes did nader get in the 2004 presidential election?

3. shall i go on?

rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

I'm with Rudy, some dat inherently requires null as you will not know the value at the time the intial data is input. Example -Start date is proabbly known at the time data is intially input, end date is not. To put int a fake date such as 1/1900 would be incorrect and misleading. Null is the only correct thing to use here.

Questions about posting. See faq183-874

 

[harebrain]

To put int a fake date such as 1/1900 would be incorrect and misleading. Null is the only correct thing to use here.

Hold on, Sister, ;-) nobody proposed such a thing as a "fake date." That would violate domain integrity and render the table non-relational! And null itself can be incorrect or misleading: do you not know the end-date, is there no end-date, or has it just not arrived yet? Of all the inferences we can draw from this lack of a value, which is correct? You don't know.

The correct alternative would be to remove attributes (such as end-date) from the table: they belong in a different table, where the row wouldn't exist until its attribute(s) were capable of being populated.

So no, no data ever requires nulls provided that the schema is correctly normalized. For further exposition, see Date's work.

 

[r937]

The correct alternative would be to remove attributes (such as end-date) from the table: they belong in a different table, where the row wouldn't exist until its attribute(s) were capable of being populated.

nice theory, cr@ppy in practice, and i will bet you a doughnut that you do not yourself design tables this way


if you do, good luck remaining gainfully employed



suppose you wanted your database to record the companies with which you do business

let's say you had a company for which you did not have an address

oops, can't use nulls, split address into its own table

let's say you had a company for which you did not have a phone

oops, can't use nulls, split phone into its own table

let's say you had a company for which you did not have a contact

oops, can't use nulls, split contact into its own table

see where that's headed? you will need to join seventeen tables just to list your flippin' companies!!!

i'm sorry, but that's just ludicrous


rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

Then you end up having joins that are unnecessary to see the data. Joins are more costly than nulls. And the more possible null fields the more tables you would have to have. So you can have one table with 20 fields which might contain a null or 21 tables to join every time you want to see the data. That would lead to slow joins and probably to lots of errors in retrieving data because it is hard to remeber all the tables you need to join to.

Null by definition means unknown. Why the data is unknown is basically irrelevant.

I have worked with databases of all types from large mainframes to Access for well over 20 years (literally thousands of different databases as I analyzed data for Navy Audit and Navy Manpower for many years) and have never yet run across one which succeeded in eliminating nulls.

Questions about posting. See faq183-874

 

[r937]

oh, and you know what? suppose you did split your company data into seventeen tables

and suppose you did (can't think why) want to produce a listing of all the companies with all their attributes

you'd have to use left outer joins, or else the only companies you'd get back from the query are those that had all seventeen attributes

and with left outer joins, guess what you get when a matching row isn't found?

that's right, nulls



rudy

http://r937.com/

SQL Consulting

 

[harebrain]

From above:

the answer depends on whether you subscribe to the school of thought that accepts null as a legitimate place-holder where a value ought to be, or to the school that rejects null outright. The second school is more current, the first seems more practical in this case.

So, Rudy, SQLSister, what exactly do you want from me?

 

[SQLSister]

I think we were just pointing out that the use of nulls is valid and very current and certainly practical.

Questions about posting. See faq183-874