You’re on the right track by breaking address out into its own table. I’d add a couple of additional suggestions. Consider taking the Address FK columns out of the Customers/Orders tables and creating junction tables instead. In other words, treat Customers/Addresses and Orders/Addresses as many-to-many relationships in your design now so you can easily support … Read more
By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. You can make them case-sensitive by using quotes around them (eg: SELECT * FROM “My_Table” WHERE “my_field” = 1). SQL keywords (SELECT, WHERE, JOIN, etc.) are always case-insensitive. On the other hand, string comparisons are case-sensitive (eg: WHERE field=’STRING’ will only match columns where … Read more
Here is the way I would design the database: Visualization by DB Designer Fork The i18n table only contains a PK, so that any table just has to reference this PK to internationalize a field. The table translation is then in charge of linking this generic ID with the correct list of translations. locale.id_locale is … Read more
I’m with the Normalize camp. Here are a few hints to get you started: Start with a process to assign some arbitrary unique identifier to each “person”. Call this the PersonId or something like that. This identifier is called a surrogate key. The sole purpose of a surrogate key is to guarantees a 1 to … Read more
According to section 10.2 of the MySQL 5.1 Manual: In non-strict mode, when an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a … Read more
I think django-fixture-magic might be worth a look at. You’ll find some additional background info in Scrubbing your Django database.
You can do it using the mysqldump command line utility: mysqldump -d -u <username> -p -h <hostname> <dbname> The -d option means “without data”.
Generally, int. bigint if you think you’ll have more rows than there are atoms in the universe. uniqueidentifier is useful if you need globally unique keys (keys that are guaranteed to be unique across all tables in your schema, maybe even universally unique (I don’t remember)) The other two I wouldn’t use they’re not integral … Read more
What you have there is called a junction table. It is also known as: cross-reference table bridge table join table map table intersection table linking table link table But I’ve never seen the term “lookup table” used for this purpose.
There is a difference between EAV done faithfully or badly; 5NF done by skilled people or by those who are clueless. Sixth Normal Form is the Irreducible Normal Form (no further Normalisation is possible). It eliminates many of the problems that are common, such as The Null Problem, and provides the ultimate method identifying missing … Read more