crimsonsun Posted March 7, 2009 Share Posted March 7, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/148362-my-proposed-db-design-a-q-about-identifying-remaining-keys-snapshot-of-layout/ Share on other sites More sharing options...
corbin Posted March 7, 2009 Share Posted March 7, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/148362-my-proposed-db-design-a-q-about-identifying-remaining-keys-snapshot-of-layout/#findComment-779122 Share on other sites More sharing options...
crimsonsun Posted March 8, 2009 Author Share Posted March 8, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/148362-my-proposed-db-design-a-q-about-identifying-remaining-keys-snapshot-of-layout/#findComment-779415 Share on other sites More sharing options...
corbin Posted March 8, 2009 Share Posted March 8, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/148362-my-proposed-db-design-a-q-about-identifying-remaining-keys-snapshot-of-layout/#findComment-779707 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.