Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> Dopasowanie kluczy do tabel
trifek
post 12.09.2018, 20:52:01
Post #1





Grupa: Zarejestrowani
Postów: 189
Pomógł: 0
Dołączył: 28.09.2015

Ostrzeżenie: (0%)
-----


Witam serdecznie,


Mam takie tabele:

  1. CREATE TABLE `psAdmin` (
  2. `id_admin` int(10) UNSIGNED NOT NULL,
  3. `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  4. `company_id` int(11) NOT NULL,
  5. `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  6. `password` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  7. `salt` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  8. `last_logged_in_date` datetime NOT NULL,
  9. `valid_account_in_time` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  10. `expiration_date` date NOT NULL
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  12.  
  13.  
  14.  
  15. CREATE TABLE `psAdminDetails` (
  16. `id_admin_details` int(10) UNSIGNED NOT NULL,
  17. `id_admin` int(10) UNSIGNED NOT NULL,
  18. `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  19. `company_name` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  20. `nip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  21. `street` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  22. `city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  23. `post_code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  24. `country_id` int(11) NOT NULL DEFAULT '0',
  25. `provincial_id` int(11) NOT NULL DEFAULT '0',
  26. `avatar` varchar(65) COLLATE utf8_unicode_ci DEFAULT NULL,
  27. `level` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  28. `notes` text COLLATE utf8_unicode_ci
  29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  30.  
  31.  
  32.  
  33. CREATE TABLE `psAdminLoginAttempts` (
  34. `id_admin_login_attempts` int(10) UNSIGNED NOT NULL,
  35. `id_admin` int(10) UNSIGNED NOT NULL,
  36. `time` datetime NOT NULL
  37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  38.  
  39.  
  40.  
  41. CREATE TABLE `psAdminLoginHistory` (
  42. `id_admin_history` int(10) UNSIGNED NOT NULL,
  43. `id_admin` int(10) UNSIGNED NOT NULL,
  44. `time` datetime NOT NULL,
  45. `ip` varchar(25) COLLATE utf8_unicode_ci NOT NULL
  46. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  47.  
  48.  
  49.  
  50. CREATE TABLE `psCompany` (
  51. `id_company` int(10) UNSIGNED NOT NULL,
  52. `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  53. `name` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL,
  54. `nip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  55. `notes` text COLLATE utf8_unicode_ci,
  56. `street` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  57. `city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  58. `post_code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  59. `country_id` int(11) NOT NULL DEFAULT '0',
  60. `provincial_id` int(11) NOT NULL DEFAULT '0',
  61. `contact_person` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  62. `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  63. `www` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  64. `phone_number` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  65. `date_of_registration` datetime NOT NULL
  66. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  67.  
  68.  
  69.  
  70. CREATE TABLE `psProvincial` (
  71. `provincial_id` int(10) UNSIGNED NOT NULL,
  72. `name` varchar(35) COLLATE utf8_unicode_ci NOT NULL
  73. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  74.  
  75.  
  76. ALTER TABLE `psAdmin`
  77. ADD PRIMARY KEY (`id_admin`);
  78.  
  79.  
  80. ALTER TABLE `psAdminDetails`
  81. ADD PRIMARY KEY (`id_admin_details`);
  82.  
  83.  
  84. ALTER TABLE `psAdminLoginAttempts`
  85. ADD PRIMARY KEY (`id_admin_login_attempts`);
  86.  
  87.  
  88. ALTER TABLE `psAdminLoginHistory`
  89. ADD PRIMARY KEY (`id_admin_history`);
  90.  
  91.  
  92. ALTER TABLE `psCompany`
  93. ADD PRIMARY KEY (`id_company`);
  94.  
  95.  
  96. ALTER TABLE `psProvincial`
  97. ADD PRIMARY KEY (`provincial_id`);
  98.  
  99.  
  100. ALTER TABLE `psAdmin`
  101. MODIFY `id_admin` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  102.  
  103.  
  104. ALTER TABLE `psAdminDetails`
  105. MODIFY `id_admin_details` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  106.  
  107.  
  108. ALTER TABLE `psAdminLoginAttempts`
  109. MODIFY `id_admin_login_attempts` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  110.  
  111.  
  112. ALTER TABLE `psAdminLoginHistory`
  113. MODIFY `id_admin_history` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  114.  
  115.  
  116. ALTER TABLE `psCompany`
  117. MODIFY `id_company` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  118.  
  119.  
  120. ALTER TABLE `psProvincial`
  121. MODIFY `provincial_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  122. COMMIT;
  123.  




tabele mają następujące funkcje:
- psAdmin - tabela loginów administratorów
- psAdminDetails - tabela danych szczegółowych administratorów
- psAdminLoginAttempts - tabela z próbami logowania administratorów
- psAdminLoginHistory - tabela z historią logowania administratorów
- psCompany - baza firm. Firm może być nieskończenie wiele. Do każdej firmy można przypisać administratorów
- psProvincial - województwa


Czy dodalibyście jeszcze jakieś klucze (np. obce)? Jeśli tak, to gdzie?
Jest sens łączyć:
1. psAdminLoginAttempts.id_admin = psAdmin.id_admin
2. psAdminLoginHistory.id_admin = psAdmin.id_admin
3. psAdmin.company_id = psCompany.id_company
4. psAdminDetails.id_admin = psAdmin.id_admin
5. psAdminDetails.provincial_id = psAdmin.id_admin
6. psAdminLoginAttempts.id_admin = psAdmin.id_admin
7. psAdminLoginHistory.id_admin = psAdmin.id_admin

kluczami obcymi?
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 19.09.2018 - 14:36