Maker your own Database... Experience?

Status
Not open for further replies.
n3ablas said:
Read a book on database design and structuring.

The proper fields and assignment of lengths can have a huge impact on the performance of a database. Just think for each field you assign an extra character - that's a wasted bit used if you don't populate it.

Example:
using a varchar(50) for a zipcode - obviously varchar(10) would cover it. So instead of wasting 40 bits per record in that one table, you've optimized it. In a 10 million row table - that's about 381 mb you save from that one field, imagine if you've got 10 fields not optimized.

There is really a lot behind designing a well functioning scalable database. Like I said - read a book on the subject.
Click to expand...

First of all, it's bytes not bits.

Second, a varchar(N) field only requires 1 byte above what is actually stored (2 bytes if N > 255), 'N' is the maximum allowed characters and any data above that number is truncated.

In your example, if all that was stored was a zip + 4, such as 12345-1234 (10 chars), both varchar(10) and varchar(50) would require the same storage space, 11 bytes.

Looks like you need to read a book on the subject.
 


I can't edit anymore...
LogicFlux said:
A table to store user_id and project_id will allow many to many relationships.
Users will be able to have more than one project and projects will be able to have more than one user.

Not sure if that's what you meant. But I would probably take the user_id out of the project table. It might be faster or more efficient to put the user id in the project table(I'm not sure) but you're painting yourself into a corner of having to have one user per project.
Click to expand...

I think this is what Charcoal meant and I agree with you. What I'm beginning to understand now is that when dealing with DBs (and this was mentioned before) - it's better to distribute data and references across different tables rather than trying to "save space" and consolidate things like e.g. with concatenations in programming. It's about storing data in a most accessible way, not programming per se.


xmcp123 said:
My favorite format:

* ...

Instead of formally inserting, call getVariableId("myfield", $value);
That function will (no matter what) return the id in table "myfield" where "data" is $value.
So it calls a select. If the value doesn't already exist, it inserts it into the database THEN gets the id and returns it.
Click to expand...

This is a great concept indeed, I can see the advantages of having a master table that only stores references. Combined with classes to handle it all, this model looks like heaven to me. I will definitely put the idea to good use. Thanks for sharing! And a big +rep
 
@jeanpaul, thanks for the book tip. I'll check it out. From what I can see it looks very helpful.

I may have to wait a day before I can give more rep, but it's coming :)
 
yes, that's basically what you do
see Snowflake schema - Wikipedia, the free encyclopedia

summarized: split everything into table based information, no redundant information when possible
an example, say a car dealer website:
the table cars could contain the following records

ID - Make - Model - Extcolor - Intcolor
1 - Nissan - Maxima - White - Black
2 - Dodge - Ram - Red - Black
3 - Ford - Taurus - Black - Black
1 - Nissan - Altima - White - Red

this would be all text, and the same piece of informtion is repeated thousands of times

a better structure would be:

Table cars:
1 - 1 - 1 - 1 - 2
2 - 2 - 2 - 3 - 2
3 - 3 - 3 - 2 - 2
4 - 1 - 4 - 1 - 3
Table Makes:
1 - Nissan
2 - Dodge
3 - Ford
Table: Models
1 - Maxima
2 - Ram
3 - Taurus
4 - Altima
Table Colors:
1 - White
2 - Black
3 - Red

this way you save space, have a very flexible structure and don't have redundant information which would make your db grow making it slower

this is a very basic example but it should give you the idea behind this stuff

btw you don't have to worry about this stuff if you don't have a huge project
 
  • Like
Reactions: bobsoap
I was just curious because I have heard people with opposite views on it, but do any of you explicitly set foreign keys in MySQL? I know it can be done with the InnoDB tables, but some people claim to not use them because it affects performance.
 
I'm a bit rust on my SQL but I think foreign keys are for enforcing referential integrity. For example using Ice Cube's DB if we tried to set 4 for extcolor or intcolor in the cars table it would fail because there's no entry for 4 in the colors table. If you didn't have it as a foreign key you could still insert a 4 into the table cars.

And this would prevent you from deleting ID 3 from the colors table if it were in use in the cars table.
 
Yes... use InnoDB and foreign keys. I've never seen the numbers, but a small trade in performance is worth it. After decades of designing databases that is the only way I would ever due it.
 
I never bother with foreign keys for referential integrity. If you use them you always have to set up error handling to trap unexpected errors. And you never know what the error's going to be so the handling ain't graceful.

Far better to validate beforehand. At least the user gets a decent error message that way.

But then, I am old-fashioned.
 
Icecube said:
yes, that's basically what you do
see Snowflake schema - Wikipedia, the free encyclopedia
Click to expand...

Great resource. I believe that no matter where you start out - and this not only applies to databases -, you should always keep scalability in mind. You should always aim at +100% of the goal you evision... Or at least leave room for the possibility. I was a part of such opportunities coming up more than once. You never know what will come along or what will happen next.

+rep and thank you for adding this.
 
Status
Not open for further replies.
Top Bottom