Two (or more) tables or just one?

[sfjerry]

Hello Folks,

I'm designing a db for a website that will utilize SQL Server 7. My question is what are the factors and issues involved when considering whether or not to segregate the records in one large table into two or more tables?

Consider the following two models (mock examples):
------------------------
Method 1 -- One Table)
--Table: "Ads" {w/ 35,000 car and boat ad records}
----Fields: "AdID", "AdTitle", "AdText", "AdStatus"
------------------------
Method 2 -- Two Tables)
--Table 1: "CarAds" {w/ 30,000 records}
----Fields: "AdID", "AdTitle", "AdText", "AdStatus"
--Table 2: "BoatAds" {w/ 5,000 records}
----Fields: "AdID", "AdTitle", "AdText", "AdStatus"
------------------------
Assume that: 1) car ads are read much more frequently than boat ads, 2) records are updated occasionally, and 3) the system is running at near capacity with one large table.

What is the better way to go?

My sense is that Model 2 might be better since "car" records will not be hampered by locks, reads, and writes for "boat" records and vise-versa. Of course this is not the academic way of doing things, but it might make sense in light of practical perfomance issues.

I guess it begs the question: what can a dbms handle better - one table with M+N records, or two tables with M and N records?

Lot of Q's for sure.

Thanks in advance, Jerry

 

[tlbroadbent]

Check out my selection of SQL articles at

http://members.home.net/tlbroadbent/sqlarticles.htm.

It includes some articles about database design.

The SQL Books Online contain a lot of good guidelines. Check the topic "Database Design Considerations."

I also recommend searching the Micrsoft web site for additional database design articles such as the next two.

Designing a Database -- Understanding Relational Design

http://www.microsoft.com/TechNet/Access/technote/ureldes.asp

Fundamentals of Relational Database Design

http://www.microsoft.com/TechNet/Access/technote/ac101.asp

Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[cdukes]

A third design would be as follows:

--Table 1: "Adverts"
----Fields: "AdID", "AdTitle", "AdType", "AdStatus"
--Table 2: "AdvertText"
----Fields: "AdID", "AdText"

Where AdType will be either 'Car' or 'Boat' or any other type that might need to be added later.

One of the slowest queries in SQL is retrieving large text fields. The above design allows you to independantly retrieve the titles of the adverts, without having to retrive the text as well (unless you want to). The Adverts table will be fairly small and quick to retrieve data from as it does not contain any text fields. The design also gives you the ability to split the AdvertText table if required at a later date if necessary ( ie AdvertTextCar & AdvertTextBoat)

Hope this helps,

Chris Dukes