Database schema question

[Recoil UK]

Hi guys

Just starting out on the database design process, and I thought I would post on here so you can verify my thinking.

I,ve been designing a customer database, with the schema below file attached.

I,ll post a second message with the question, as it requires 2 files.

L8r

[Recoil UK]

Here,s the second file.

My question is this....

Because not all customers will require an address, and those that do can only have one, i've change the pk in addresses to customer_id and changed the table name to customer addresses.

But I want to keep my fk filed in the address_lines table as address_id, am I correct in thinking I can do this so long as my address_id field in customer_addresses remains an auto increment field?

Also can anyone spot any flaws in the overall design?

Any thoughts, tips, suggestions would be most welcome

Thx guys

[Spookster]

I would get rid of the customer types table and just add type to the customer table since you only have one attribute for it. Normalization is good to reduce data redundancy however but on the other hand too much normalization can also reduce overall system performance.

[Recoil UK]

Hi

Thx for the reply.

I think I will do that, as it will only have 3 possible values and what goes in that field is dependant on which webpage the account is created from, and can be controlled by PHP.

My second question regards mySQL Primary Keys and is this...

Do primary keys have to auto-increment? is it not possible to insert a primary key value from a primary key from another table.
I know you would not normally do this, as it describes a one-one relationship, but as you can see some customers dont have an address at all.

Or is the address_id required?

Thx again

[Spookster]

The Primary key does not need to be an auto-increment field but it must contain unique values which is why auto-increment is often used. If you are not going to use auto-increment then using some kind of number that will be unique for a particular person such as the persons ID number, drivers license number, etc.


If a person does not have an address then there is no point in creating a record for them in the address table.

[Recoil UK]

Hi again

Yes your right, maybe I didnt explain.

In the 3rd schema for the addresses table, i,m using customer_id as the primary key, which is in turn the primary key from the customers table (a not null, auto increment value).

Therefore if a customer doesnt require an address then no entries will be input into the address tabel.

I just needed to verify that this was possible.

Thx very much.

[kestrel7]

Hi

This type of situation arises often in relational database design. What you are describing is called a foreign key. Foreign - because it is a primary key of one table that you include as an attribute in a second table.

Most database systems allow you to specify foreign keys when you create the tables (much the same as you would declare a primary key for a particular table). The advantage of doing this, is that the system will ensure that any key inserted as a foreign key is in fact a valid attribute value (i.e it does appear as a primary key in the table that you are referencing). In other words, it will not allow you to put a customer_id, (say 007), as a foreign key if there is no such customer_id (007) appearing as a primary key.

I am not sure of the sql to declare foreign keys in mysql (it should be in the manual) but it should not be too dissimiliar to the way you delcare primary keys ( try something like... foreign key(customer_id) referencing [name_of_table_that_contains_customer_id_as_a_primary_key] or something similiar)

Hope this helps

[Spookster]

mysql does not support foreign keys. Although mysql is considered a relational database system it is not a true RDBMS though. To ensure data integrity all cascading of data insertion or deletion or updating must be hardcoded with whatever server side language you are using.

[kestrel7]

Hi

ooops ... sorry about that! Thanks for clarifying Spookster. I learn something new everyday.... that'll teach me to reply to something I haven't actually tried myself!

cheers