My proposed DB design, a Q about identifying remaining Keys. Snapshot of layout.

[crimsonsun]

Hi All,

 

This is my first post, my friend yandoo recommended I visit here. I have been developing an application, part of the design is a MySQL database and I have a few questions about my proposed design.

 

Here is what I am suggesting:

 

 

I'll try my best to describe relationships..

 

A config can have 1 of many types.

A config can have many deviceTypes.

 

A device can have 1 deviceType.

 

A project can have many Devices.

A project can have many users.

 

ProjectLead ~ Username.

In configs table; configTemplate & deviceConfig are Booleans.

In devices table; deviceConfig ~ configID (given deviceConfig in configs table = true)

 

I think the rest is explained via the table layout in the picture.. Please ask if you have any more questions..

 

So here are my questions:

 

[*]Firstly, so far with what I've done, have I already mucked up somewhere in the design?

[*]Secondly, I've identified the primary keys for the columns highlighted in yellow, I plan for these to all auto increment, beside Username. I'm having trouble deciding the Primary keys for the remaining tables and identifying any Forgien keys.

[*]Lastly, I know I must use InnoDB in phpMyAdmin to use Forgien keys but I'm not sure what the correct name is to do so; I only see Unique & Index options..

 

Any help would be really greatly appreciated. Thank you, and I look forward to learning and being able to bring something back into this community.

[corbin]

Don't store user names as the lead.  Store user IDs.  (Have an autoincrementing field for user_id or something in the users table.)

 

 

 

Also, why are you bothering to have a configTypes table if you're just going to have 1 column?

 

 

It's good how you separated things out, but it seems that you like using textual keys, which is often not optimal.

[crimsonsun]

Heya Corbin, Thank you for your reply.

 

I shall add a userID for the Users table. There are deviceTypes & configTypes tables that will store Types and easily allow addition of new Types (i.e. via admin panel).

 

I am having trouble identifying the Keys and what type of Keys for the ConfigDevices, ProjectDevices & ProjectUsers Tables. Can you provide any insight?

 

Regards,

CrimsonSun.

[corbin]

Will Configs.configType and ConfigTypes.configType always be a 1 to 1 relationship?  What about the same thing with deviceType?

 

 

I'm not sure what the purpose of ConfigDevices is.  I'm sure it has a purpose, but I don't see it.

 

 

 

As for the ProjectDevices and the ProjectUsers tables, those are related via protectID, yes?  Oh!  I think I see the relation now with ConfigDevices.  It would be related ConfigDevices.configID -> Configs.configID -> ProjectDevices.configID.  Then ProjectDevices.projectID -> Projects.projectID.  Then Projects.projectId would related to projectUsers and so on.

 

 

 

So uhmmm....  I'm not quite sure what your question is?  What kind of keys?