Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Links SQL : Database Design, Configuration + Multi-Cat

Quote Reply
Links SQL : Database Design, Configuration + Multi-Cat
Hello Everyone!

This topic was posted by me in Discussion forum and was relevant but I post it again here as it is more relevant to this forum which has been newly created only recently.

Question :
1 - Table lengths :- Fixed or dynamic or variable
Links SQL has a fixed colums design of the database. Why? Does this not require more space? When having 1,00,000 rows, every byte of space becomes more important.

2 - Multiple Databases ./. Single Database :-
What is more efficient - more Databases on the server and less tables or everything could be in the same database with different tables.

3 - While adding new definitions one has to give the VARCHAR / The size. Does this really matter?

4 - If I have 1,00,000 links in a table with all the normal information that the fat file makes from v2.0, for e.g. Then would it not be better to have it fragmented for the performance of SQL? I mean if I have the Key ID + few more data in the table and then further divide them, that would offer better performance.

While studying the routines of Links, I notices that conceptually, it takes Category/Category/Category... (Cat or Alt_Cat) And then the script ""Cleans it"".

My suggestion :- If from the begining if there was a system that there is no trailing slash names of the categories but simple names, for e.g. Instead of Canada/TheBeautiful/Vancouver/Says/OnTheHomepageOF/Gossamer if it was

Canada
TheBeautiful
Vancouver
Says
OnTheHomepageOF
Gossamer

Then all these could be related to anather set of hierarchies. Each set of hierarchy has a theme and they could be linked or grouped to form a bigger Matrix thereby giving a structure to buildup the ""Categories_Unclean""
This would give a system or a concept of Multi-Categories. May be...........




------------------
rajani











Quote Reply
Re: Links SQL : Database Design, Configuration + Multi-Cat In reply to
Hi,

1. Fixed length columns are considerably faster then variable length columns, and besides disk space is cheap. Smile

2. Really depends on the scenerio. Mysql uses files to store your data, so you are usally bound to the o/s file size limit (which on Linux is 2 GB). This means a single table can't be bigger then the max file size. In most cases this doesn't matter.

3. The size represents the length of the field. Mysql and many other servers can't search on fields > 255 chars, or you need to switch to BLOB/TEXT type fields.

4. It depends on what sort of performance you are looking for? If you wanted to search, it would be easier/quicker to search over the entire table then to search over each smaller table individually.

5. Category Question - This doesn't work as nicely with the admin interface as the single table model does. It would make it more difficult to add in new fields to the Category table.

Cheers,

Alex

Quote Reply
Re: Links SQL : Database Design, Configuration + Multi-Cat In reply to
Hello Alex!


Quote:
1. Fixed length columns are considerably faster then variable length columns, and besides disk space is cheap.

Reading from the book MySQL and mSQL by o'Reilly published recently, I have seen on the page 98 Chapter 6: SQL According to MySQL and mSQL, it mentiones that the storage space of CHAR(150) is really 150 bytes and VARCHAR(150) is only 31 bytes!!! This is what I meant. But may be performance question is more important as you rightly suggested and ofcourse you have more experience on that.

Quote:
5. Category Question - This doesn't work as nicely with the admin interface as the single table model does. It would make it more difficult to add in new fields to the Category table.

After knowing some more of SQL, one can select Multiple tables and also Multiple database by a single SQL command. It is just that it offers more of a security as it would then involve more passwords checks and more security.

Currently Links SQL works only with one default database. However in future it may be an idea for to make it work with Multiple database on also different server.

------------------
rajani











Quote Reply
Re: Links SQL : Database Design, Configuration + Multi-Cat In reply to
 
Quote:
Reading from the book MySQL and mSQL by o'Reilly published recently, I have seen on the page 98 Chapter 6: SQL According to MySQL and mSQL, it mentiones that the storage space of CHAR(150) is really 150 bytes and VARCHAR(150) is only 31 bytes!!! This is what I meant. But may be performance question is more important as you rightly suggested and ofcourse you have more experience on that.

If you have 150 bytes of data, it will take 150 bytes regardless of CHAR or VARCHAR. You save space using VARCHAR, as if you are only storing 70 bytes, then it doesn't require the full 150 bytes of disk space.

The downside is you get a performance hit as mysql now has to keep track of pointers to where records start as they are all variable length.

Cheers,

Alex