Table creation : big table or a lot of small ones

[Hydex]

I want to create a dynamic page which will be composed of quite a lot of elements and will be filled with elements from my database.
First is the headline, 10 or so attributes, separate. Then 30 lines on the same model : Title (different for each line of course), Boolean, String, Date, Another String, Another Date, Another String. These lines are independent.
There will be approximately 1000 pages.
I wonder if I should create one huge table with everything in it (like Id, Headline, Title1, Boolean1, String1, Date1, String1, Date1.2, String1.2, Title2, Boolean2, String2, Date2, String2, Date2.2, String2.2, ...) or one table for each line (on the model : IdPage, Title, Boolean, String, Date, Another String, Another Date, Another String) plus the headline in a separate table?
I think I should take the "lot of tables" solutions, am I right? will it be a problem for Access?

 

[The_Doc_Man]

This is a case where normalization and performance might send you in different directions. Normalization tends to split tables towards the "many" side of the equation. Performance optimization tends to join tables towards the "fewer" side of the issue.

I'm afraid this is going to be your call as to what works for your. Try a sample each way and see how it goes.

Note that if the database was not being web-accessed, but was rather a simple shared Access app over an intranet, I would direct you towards normalizing the tables. For web apps, it CAN be a tougher call.

I'm sure that with some experimentation you can strike a happy medium. Though why you should assault a jolly spiritualist as per that idiom is beyond me.

 

[Hydex]

I'm trying right now, thanks .
Concerning the "many tables" solution:
Only strange thing is that Access doesn't want my one on one relations between the Id of the main table (with the headline) and the Id of the small tables. Well it creates the link, but I cannot use any referential integrity, it says there's an error somewhere...

 

[The_Doc_Man]

When you have a lot of one//one tables, that probably should not have been split. (Your question and situation were not clear.) When you have data that repeats a lot such that a one//many or many//one table relationship is possible, that is where table splits make a lot of sense.

Access will impose some limits on you, though. Neither tables nor queries can exceed limits of 255 fields per table/query, nor can a single record exceed the size of an internal disk buffer - which depends on your Access version. Look up "specifications" for AC2k and later, or "limits" for AC97 and earlier.

If you need a SINGLE RECORD holding more than the amount of data in a single buffer, you are hosed. You will HAVE to play some tricks to get there from here.

 

[boblarson]

I'm trying right now, thanks .
Only strange thing is that Access doesn't want my one on one relations between the Id of the main table (with the headline) and the Id of the small tables. Well it creates the link, but I cannot use any referential integrity, it says there's an error somewhere...

Your keys are probably not set correctly.