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!

 

[MDXer]

As to whether null is acceptable also depends on the type of DB you are designing. Different database structures call for different design options. If a dimension member has a StartDate and EndDate but the EndDate has not arrived or been entered then I will leave it NULL and update it as a Type1 Slowly changing dimension.

To make broad generalizations that are neither 100% right or 100% wrong only make it more difficult for newer people to this area of technology to get a more complete understanding.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[harebrain]

As does failing to acknowledge opposing points of view. But anyone who expects "complete understanding" could do much better than post questions here.

 

[r937]

yeah, for "complete understanding" a trip to dbdebumph.com is mandatory



rudy

http://r937.com/

SQL Consulting

 

[harebrain]

Where, oh where did we stop helping each other and start a p*ssing contest? Oh, yeah, right about where you owe me a doughnut.

 

[r937]

harebrain, i'm sorry you feel so slighted

feel free to look me up in toronto if you're ever in this, the most beautiful city in the most wonderful country on the planet

i'll not only buy you a doughnut, i'll buy you a beer or three (if you're a drinker)

and you can rant and rave about NULL all you want, i will certainly listen politely



rudy

http://r937.com/

SQL Consulting

 

[lespaul]

I have to say that Albuquerque is the most beautiful city in the (almost) most wonderful country on the planet, especially this time of year with the International Balloon Fiesta


Leslie

 

[harebrain]

I spent a week in Albuquerque one night.
Rimshot!

 

[DanJR]

This subject is one of two main areas that I would like to know more about - the other is the academic and practical implications of using (or not using) surrogate keys!!

C J Date has a strong and differing view from Codd with respect to the introduction of NULL's in the relational model. His differing view is obvious when reading his book "The database relational model: a retrospective review and analysis" (2001) e.g.

"...I believe nulls and everything to do with them to be a mistake (in my oponion Codd's one big error of judgement in this whole business"

Thus, its a pitty that his article on null values hasn't undergone peer-review. Or maybe thats a good thing given the possibility of publication bias? I think Date may represent an extreme view(?)

Does any know of any peer-review literature review articles (note "review") on either this article or the use of surrogate keys?

Cheers,
Dan

 

[harebrain]

Dan,

As far as I can tell from searching the literature, Date seems to have given up on the peer-review process for his work. He seems fed-up with the nature of discourse, which has degenerated from the scientific approach to a "religious war." (Gee, who'd have guessed?) His latest article in the ACM library is dated 1998; these are not new works, rather self-defense where others have attacked him. The Third Manifesto, written in 1995 with Hugh Darwen, seems to be the final attempt at working within the scientific community. (It's worth reading if you have access to the ACM digital library.)

Note that the above statements constitute inferences that I've drawn from the evidence at hand. I have no pipeline to Date, and I could be wrong.

The newer works in the library attempt to solve the problems of the three interpretations of nulls (7-value logic.) These contortions make Date's "extremist" views (pun not intended) seem elegantly simple.

For those of you who might not know (and are readying your flame-throwers) Date distinguishes between the relational model and SQL. They are not the same.

--David

 

[DanJR]

hi harebrain,

sorry for taking so long to get back to you. Thanks for the information - yes, i do have access to the ACM library, so i will look up the article.

Thanks
Dan

 

[bethae3]

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.

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 would create:
1. a product table that held the name and data that is consistent between products linked to
2. a product type table,
3. a competitor info table,
4. and a unique field table,
link them together so that in your main form, you could enter the constitent data, use a combo box w/subform to add unique data, add a subform or popup form with expandable competitor data related to that specific product, and have a listbox for your product type.


Bethae3
The phrase "working mother" is redundant.