Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Design Choices

Hi, I am creating an online system that stores presentations and I have some design questions. My scenario:

The website is written in ASP and I am using a SQL Server database. The Presentations are part of a larger system that also stores articles, members and about 20 to 30 other tables. The number of presentations stored will be quite large. It should be able to accommodate upto a million of them (!). Furthermore the table holding all the presentations has a lot of large fields, this is the list without the actual field names:

Int(Identity)
Int
Int
NVarChar(20)
NVarChar(20)
NVarChar(20)
NVarChar(20)
NVarChar(250)
NVarChar(500)
NVarChar(800)
NVarChar(750)
NVarChar(750)
NVarChar(750)
NVarChar(300)
Decimal(10,7)
Decimal(10,7)
Int
Int
NVarChar(80)
NVarChar(250)
NVarChar(3500)
NVarChar(3500)
NVarChar(20)
NVarChar(30)
NVarChar(30)
NVarChar(30)
NVarChar(1600)
NVarChar(1600)
Int
Int
Int
Int
Decimal(9,2)
NVarChar(3)
Decimal(9,2)
NVarChar(20)
NVarChar(400)
NVarChar(100)
NVarChar(100)
NVarChar(100)
NVarChar(100)
NVarChar(100)
NVarChar(450)
NVarChar(450)
NVarChar(450)
NVarChar(450)
NVarChar(450)
NVarChar(25)
Int
NVarChar(100)
Int
Int
Int
Int
Bit
Bit
NVarChar(12)
DateTime
DateTime

As you can see each record is going to be quite large! Besides so-called Full Presentation, the system will also allow the entry of Mini Presentations which adhere to the same data model but leave a lot of fields empty. In fact the datamodel of the Mini Presentations will only be something like this:

Int(Identity)
Int
Int
NVarChar(20)
NVarChar(20)
NVarChar(20)
NVarChar(750)
NVarChar(750)
NVarChar(750)
NVarChar(300)
NVarChar(80)
NVarChar(250)
NVarChar(100)
Int
Int
Int
Int
Bit
Bit
NVarChar(12)
DateTime
DateTime

Now my design questions:

1) Should I store the presentations in the same database as the rest or should I put it in its own database. The presentations will be shown on a seperate website that has only few pages that need data from both the presentations table and one of the other tables. I am guessing a seperate database would make it run smoother, right?

2) Should I store both the Mini Presentations and Full Presentation in the same table or should I split them up? The advantages of having them in the same table would of course be that all my searches and listings on the website can be done with a single query, while if I split them I would have to query both tables and put the results together. De disadvantages are of course that if I put it in one table the table will become very large with a lot of records having a lot of empty fields. I know the answer to this depends on the number of projected records. As explained the current requirements states the possibility to store a total of 1 million mini and full presentations.

Let me know what you think. If you need more info then dont hesitate to ask. Any other tips you can give me? I will divide the points amongst the comments that help me most. Thanks!
0
pgkooijman
Asked:
pgkooijman
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
pgkooijmanAuthor Commented:
Actually I think I found an elegant solution:

1) I place everything in a new database. In that way if we really expand like crazy I can always move it to a seperate server

2) I split up the huge table into multiple parts. One part will be just the basics of the presentation which is where I will store both the mini and full presentations. Other tables will contain the details of the full presentations so when needed I can just join those

What do you guys think?

Philip
0
 
Anthony PerkinsCommented:
Not sure where to start.  But right off the bat you have a problem with your table.  It is in excess of 30K in length and it should not be more than 8060 bytes.  So I suggest you go back to the drawing board and normalize your table. Also consider if nvarchar is a requirement, as it takes up twice as much space as varchar.
0
 
Anthony PerkinsCommented:
>>2) I split up the huge table into multiple parts.<<
Good thinking.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
puranik_pCommented:
presentations.. are these ppt files that you are going to store in database?
If yes, why not store the files on hard disk and just store the path in database tables?
correct me if I'm wrong.

Pura
0
 
pgkooijmanAuthor Commented:
They are not power point presentations. They are various online presentations for services and products.
0
 
puranik_pCommented:
okay.
Then like acperkins, I also suggest to first break it up in different tables.
0
 
geotigerCommented:

Another point to consider is to group fields that need to be used for web page together so that you query as few tables as you can.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now