Database Design

[IrPr]

As im not such expert as well as you guys are, I've to ask some basic database designin quetions
I've to design a database for artists details, such as Biography
but just 10% of all my records have Biography data on that
there are 2 methods to me for design this table and its scripts:

1. create one table to keep all fields on that, NULL MEDIUMTEXT field for biography
   in this case my table will store one field that just used for 10% of records
   
2. create 2 tables and use one to one relationship between artists and biography
   in this case ive to use more query or use JOIN for my code

Which one is more optimized?

Another question:
I've to store two links for my tracks, low quality and high quality
Which one is more optimized?
to use one field in tracks table and use a splitter for the urls or just using serialized data
or use another table to join and use keys property?

Thanks

[Eikinskjaldi]

Originally Posted by IrPr

As im not such expert as well as you guys are, I've to ask some basic database designin quetions
I've to design a database for artists details, such as Biography
but just 10% of all my records have Biography data on that
there are 2 methods to me for design this table and its scripts:

[create one table to keep all fields on that, NULL MEDIUMTEXT field for biography
in this case my table will store one field that just used for 10% of records

this is the correct method. Having sparse data in the system is not a problem.

Another question:
I've to store two links for my tracks, low quality and high quality
Which one is more optimized?
to use one field in tracks table and use a splitter for the urls or just using serialized data
or use another table to join and use keys property?

Serialisation is always a last resort. Either do it programmatically or have two fields, one for each type.

[IrPr]

Got ya
I created my tables this way

Thanks m8

--------------- Added [DATE]1201278626[/DATE] at [TIME]1201278626[/TIME] ---------------

One another question about bitmask and bitwise in DB and queries
I've multiple status for each entry on the table, such as SHOWN or HIDDEN, PREMIUM or FREE, ...
i can add one field for each status, for example 3 TINYINT(1) or BOOL fields
[SQL]
SELECT * FROM table WHERE `x`=1 AND `y`=0 AND `z`=1
[/SQL]


another method in my mind is use one TINYINT filed for all cases
INSERT and SELECT rows using AND(&) bit operand
for example
[SQL]
SELECT * FROM table WHERE `status`&5
[/SQL]
where 1 stands for x, 2 for y and 4 for z

which one is better? is there any difference in performance?

[IrPr]

my previous post had been auto merged
so accept my BUMP

[Eikinskjaldi]

Personally I am not a fan of bits because I find them and their code obfuscating. I aslo do not know which is quicker.

My suggestion is try it both ways and use explain and some test queries to see which is better.